Moving your DPM Database

Microsoft Data Protection Manager (DPM) is part of the System Center suite of applications, and is Microsoft’s offering in the enterprise backup space.

Like a lot of System Center applications, its designed to be installed, configured and largely left to its own devices. So what happens if you want to do something like move your DPM database to another SQL server or instance? Well, Microsoft don’t officially support moving the DPM database, preferring you you take a backup of it, uninstall DPM, re-install pointing to the new instance and restore the backup over the new database.

This approach does work, however it carries the risk that if the DPM database schema isn’t the same version (maybe you forget to install a certain update, for example) you may find that DPM fails to start. Additionally, you’ll have to run a DpmSync -Sync command to verify the contents of the database against the replica points you have on disk.

If you want to move your DPM database without all that hassle, you can follow my steps below. Please note, that while I have used this method and it has worked for me, it is not supported by Microsoft, so follow it at your own risk.

This process assumes that you have already set up a second (target) SQL server that you want to move your DPM database to, and that the instance meets all the criteria for being a DPM database (SQL Server Reporting Services (SSRS) has been installed and configured, for example). You will also have install the SQL Prep element on the target SQL server; this is available from the DPM installation media.

Migrate the MSDB database to the new instance

MSDB is a system database and is created in every SQL instance whenever MSSQL is installed. This database hold all the information relating to scheduled tasks and jobs, and without this, DPM will lose all its schedules. To move this, open SQL Server Management Studio (SSMS) on the source SQL server, where the DPM database is currently held and log in as a system admin account.

  1. Expand the folder tree on the left-hand side, down through Databases and System Databases
    sys-databases
  2. Right-click the msdb database and choose Properties
  3. In the Properties window, select the Files option and scroll across until the Path field is visible
    msdb-files
  4. Make a note of the paths and the file names, and repeat this for the DPM database in the instance
  5. Stop the SQL Service for your current DPM SQL instance – this is required to release the system locks on the database files. It will also cause scheduled future backups from your DPM server to fail, which is expected
  6. Repeat the two steps above for the target SQL instance. This will allow you identify where the msdb files are in your new instance, and hence where the ones from the original instance are to be placed
  7. Take backup copies of the msdb database files in the target SQL instance, and copy the files from the original DPM instance into the correct locations on the new server

Migrate the DPM Database to the new instance

  1. Copy the files for the DPM application database from the original SQL instance, identified above to suitable locations on the new server
  2. Open SSMS on the new server, if its is not open already
  3. Right-click the Databases folder on the left-hand side and choose Attach
  4. Click the Add button and navigate through the folder tree to locate the MDF (database) file for your DPM database. Select it click OK
  5. The Database details pane at the bottom may show some errors if the files which form part of the DPM application database are not in the same locations on the new server as they were on the original. If this is the case, browse to the correct locations for these files
  6. Click OK to attach the database. If all errors relating to paths have been corrected, the database will appear in the left-hand tree

Configure DPM Database user accounts

As the databases have been copied from another server, the instance logins and database logins will not be configured correctly, and will need to be re-created.

  1. In SSMS, expand the left-hand tree, through the DPM application database to Security > Users
    database-users
  2. Right-click the machine account login for your DPM server and remove it. The machine account will be in the form of <domain>\<host>$, so in the example above, the machine account is ANDINET\DPM$
  3. If prompted, you will also need to remove the Schema for this user
  4. Expand the instance-level Security node in the left-hand tree, then expand Users
    instance-users
  5. Right-click the Logins folder and choose New Login
  6. In the Login name field, type the machine name for your DPM server. This will be similar to the example above: you will have to enter this manually, SQL Server does not support searching for machine accounts
  7. Leave the Windows authentication radio button selected
  8. Leave Default database as master and change Default language to English
  9. Select Server Roles from the left-hand menu and ensure sysadmin is selected
  10. Select User Mapping from the left-hand menu and ensure the checkboxes next to master and the DPM database are selected. The Default Schema field should reflect the User field:
    user-mapping
  11. With the entry for master selected, ensure public is ticked in the bottom pane:
    master-role-member
  12. With the entry for the DPM application database selected, ensure db_owner, MSDPMExecRole and public are ticked:
    dpm-role-member
  13. Finally, select Status from the left-hand menu and ensure the Grant and Enable radio buttons are enabled
  14. Click OK

This will re-create the login for the DPM machine account at the instance level, the user mapping on the database and the required schema for the user.

Modify DPM Server registry settings

DPM uses registry keys to store data about which SQL server and database instance it connects to. These will need to be modified to point DPM to the new server and database.

  1. On the DPM server, open the registry editor
  2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Data Protection Manager
  3. Export this key, and its sub-keys to a backup file. This will allow you to restore your settings in the event something terrible happens
  4. In the HKLM\SOFTWARE\Microsoft\Microsoft Data Protection Manager\DB key modify the following keys to reflect the new values for the new server:
    1. ConnectionString
    2. GlobalDbConnectionString
    3. GlobalInstanceName
    4. GlobalSqlServer
    5. InstanceName
    6. ReportingInstanceName
    7. ReportingServer
    8. SqlServer
      The DatabaseName and GlobalDatabaseName keys only need to be changed if you have mounted the DPM database in the new instance with a different name to its original
  5. In the HKLM\SOFTWARE\Microsoft\Microsoft Data Protection Manager\Setup key modify the following keys to reflect the new values for the new server:
    1. SqlAgentAccountName

Update local group membership

DPM uses local security groups on the server it is installed on. These control which machines are allowed to respond to the agent, and prevent clients from trespassing onto other DPM servers, or communicating with a DPM server they have not been configured to use.

  1. Open an MMC snap-in and add the Local Users and Groups snap-in to the console
  2. Expand the folder tree and select Groups
  3. Open the MSDPMTrustedMachines group and add the machine account of the new database server. Unlike SQL Server, this snap-in will allow you to search for machine account, however you will need to ensure that Computers is ticked in the Object Types search field
    object-types
  4. Click OK to confirm adding the account to the group

Update SQL Agent Sub-system locations

The locations to the various DLLs that the SQL Server Agent uses to execute tasks are stored in the msdb database. As the database in the new SQL server instance originated elsewhere, there is a likelihood that the paths reflected in msdb will not be correct and will need to be checked and updated.

  1. On the new SQL server, open SSMS and start a new query
  2. Execute the following: select * from msdb.dbo.syssubsystems
  3. Note the paths reflected in the subsystem_dll field – if SQL Server on the new instance was not installed in the same location as the original, these paths may be incorrect. If so, you will need to modify the SQL below to suit your needs, then execute it:
    sp_configure "allow updates", 1
    reconfigure with override
    update syssubsystems
    set subsystem_dll = replace(subsystem_dll,'C:\Program Files\Old SQL Path','C:\Program Files\New SQL Path')
    from syssubsystems
    where subsystem_dll like 'C:\Program Files\Old SQL Path%'
    sp_configure "allow updates", 0
    reconfigure with override

That’s it! You’re done. Just reboot the new SQL server and then the DPM server and you’ll be good to go: your data sources will be intact, as will all your history, schedules and protection groups. As noted at the start of this article, this method isn’t supported by Microsoft and you use it at your own risk, but if you have any questions or issues, please feel free to leave a comment and I’ll help as best as I can.

Share and Enjoy:
  • Digg
  • StumbleUpon
  • Technorati
  • del.icio.us
  • Twitter
  • blogmarks
  • HackerNews
  • Tumblr
  • Posterous
  • email
Bookmark the permalink. Follow any comments here with the RSS feed for this post.
Post a comment or leave a trackback: Trackback URL.

10 Responses to Moving your DPM Database

  1. Matt

    This worked perfectly! I can’t believe it. You saved me quite the headache with a SQL 2012 to 2014 upgrade with DPM.

  2. Christer

    Thank you! Even though my scenario was a little different (switching databases between sites) this guide helped me a lot!

    • You’re welcome! Glad it helped! If you have any suggestions for topics I should cover, please feel free to let me know!

  3. Jamie

    Thanks for this, managed to get 2012 R2 working on on a 2014 box with this method. Only problem is the reports are missing from reporting services. Did you move them over manually??

    • Hi Jamie,

      Reports isn’t something that I make a great deal of use of in DPM, so I didn’t move any of them over: the only reports configured were standard ones for tape usage, however they fell into disuse when the responsibility for long-term retention (tape) backups was moved to NetWorker. Unfortunately, since originally writing this article, all our DPM servers (and their SQL back-ends) have been fully rebuilt, so none of the original servers remain for me to check, however if you browse to the reporting URL presented by SSRS, you should be able to export a report definition for anything you want to move over, then import it on the new instance of SSRS.

  4. Steven Williams

    Hello!

    Thank you for this guide. I have followed it from start to finish about 3 times, and 99% certain I haven’t missed anything… but for some reason, I can’s start up DPM… I get the following error in event viewer.

    The description for Event ID 945 from source MSDPM cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

    If the event originated on another computer, the display information had to be saved with the event.

    The following information was included with the event:

    Unable to connect to the DPM database because of a general database failure. Make sure that SQL Server is running and that it is configured correctly.

    Problem Details:
    19026/08/2016 11:30:07 AMDpmThreadPool.cs163TrueSqlExceptionLogin failed. The login is from an untrusted domain and cannot be used with Windows authentication.System.Data.SqlClient.SqlException (0x80131904): Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
    at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
    at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
    at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
    at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions)
    at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
    at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
    at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
    at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
    at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
    at System.Data.SqlClient.SqlConnection.Open()
    at Microsoft.Internal.EnterpriseStorage.Dls.DB.SqlContext.Open()
    at Microsoft.Internal.EnterpriseStorage.Dls.Utils.GlobalSetting.GetGlobalProperty(String propertyName)
    at Microsoft.Internal.EnterpriseStorage.Dls.Utils.ApplicationSettings.GetDatabaseVersion()
    at Microsoft.Internal.EnterpriseStorage.Dls.Engine.CServiceModule.DoStartupValidation(CServiceModule* )
    at Microsoft.Internal.EnterpriseStorage.Dls.Engine.CServiceModule.Initialize(CServiceModule* )
    at Microsoft.Internal.EnterpriseStorage.Dls.Engine.CServiceModule.InitializeSecurity(CServiceModule* )
    ClientConnectionId:4e32914e-8a58-48bf-b22e-b6658a65dc0c

    the message resource is present but the message is not found in the string/message table

    Do you have any ideas what I’ve done wrong?!

    Thanks in advance.

    • Steven Williams

      An additional error message after the previous one.

      SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed [CLIENT: xxx.xx.x.xxx]

      xxx.xx.x.xxx being my DPM Server.

      • Hi Steven,

        I never saw anything like this when I moved any of my databases. I think the issue might lie with this part of the error stack you posted in one of your comments: “The login is from an untrusted domain and cannot be used with Windows authentication…”

        Can you confirm that both your DPM and SQL boxes are joined to the same domain, that the necessary local firewall ports are open (or that the domain profile for the Windows firewall is disabled) and that the computer account for your DPM server has sysadmin access to the relevant DPM instance where your DPMDB database is located? Note that when adding computer accounts to SQL instances, you can’t search for them in the directory, you have to add them manually.

      • Hi Steven,

        Additionally, you might find the information in the following link useful in resolving your issue:

        http://www.secretgeek.net/boring_sql_one

Leave a Reply

Your email address will not be published. Required fields are marked *

This blog is kept spam free by WP-SpamFree.