How to move the ConfigMgr 2012 site database to a new SQL server

Anurag Shukla | Support Engineer | Manageability - Microsoft Corporation

imageHello everyone, I wrote a blog post some time back that explained how to move the System Center Configuration Manager 2007 (ConfigMgr 2007) site database from a SQL 2005 to SQL 2008 (link), and here’s how to do the same with System Center 2012 Configuration Manager (ConfigMgr 2012).

This information is ‘AS IS’ and is provided for guidance to move the ConfigMgr Site Database to a new SQL Server. Note that this may not be the only way to successfully migrate the DB but it’s a method I’ve used successfully in the past. Please use this as a guide for getting the understanding of what all steps are involved in moving the Site database. Please use it at your own risk.

Since the inception of ConfigMgr 2012, a lot has changed from the SQL standpoint as we are now heavily relying on the SQL Broker Service and change tracking. Because of this, our approach also needs to change when we are planning to move the Site database to a new system.



Site Server: CM12PRI.CM12.LOCAL


SMS Provider: CM12PRI.CM12.LOCAL


Also note that CM12PRI has a named instance where I am hosting my Site database.

Backing up the SQL database and preparing the new SQL server

We will use some SQL queries to identify the SQL server versions, configurations, service packs and configurations.


Select @@version

-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2806.0 (X64)   Feb 14 2012 18:18:40   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

This shows the SQL Server version, edition and version we are running. In my case, our existing server is running on version 10.50.2806.0. This version number will vary based on the edition you are using and on which SQL Cumulative Update your server is running. The following query can also show you the same details about the SQL Server.

--- Verify the Product version, Product Level, Editions

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The last query we will use identifies some core features that are required for ConfigMgr 2012 (e.g. whether SQL Broker is enabled or if the database is marked as trustworthy, etc.).

select name, collation_name, user_access_desc, is_read_only, state_desc, is_trustworthy_on, is_broker_enabled,is_honor_broker_priority_on from sys.databases


Here we can see various properties of our Site database server. It has SQL Broker enabled, plus the database is marked as trustworthy and is honoring the SQL Broker priority.

Step 1:

Before we move our ConfigMgr 2012 database to a different site system, we should create a current backup of the database.  Before you start the SQL backup, ensure that you run PREINST /STOPSITE to stop the Site Components. Here is what you will see, when you’ll run that command:


Step 2:

Now we will use the SQL Management studio to back up the database. Using the context menu on the Database, click Backup and you’ll see a page like the one below. Choose Full as the Backup type. You can place it on a network share, however in my case I will go-ahead and keep it local under c:\bkp\cm_pr1bkp.



Now it’s time to prepare our new SQL Server. In my lab this is named WINDOWS-S2TH386 CM12.LOCAL.

The new SQL Server should be running the same or higher version of the previous SQL Server. It’s possible to have a SQL Server that was running the Cumulative Update 4 (source database) and now you’re moving the database to a server that is running Cumulative Update 6, however if you are moving to the same version of the SQL server, please try to keep them alike to avoid any unwanted results.  


Before you restore the ConfigMgr database on the new SQL Server computer, please verify Server Collation setting by doing the following:

a. Open Microsoft SQL Server Management Studio.

b. Choose Connect.

c. Right-click on your Server Name and choose properties.

d. Check for the following:


e. The server collation settings should match those on our old SQL Server.

Make sure that the CLR integration is enabled.  To check that run the following stored procedure:

sp_configure 'clr enabled'

Look for the RUN_VALUE - if that is marked as 1 then it means CLR is enabled. Once you have verified these settings, let’s move on to the next step.

Restoring the database on the new SQL Server

In my lab, the new SQL Server is named WINDOWS-S2TH386 CM12.LOCAL

1. Click on Restore Database under the database node in SQL Management Studio as shown below:


2. On the restore database page, choose the option “From device”:


3. Provide the backup file that was created in the previous step. I had already copied the backup file to the WINDOWS-S2TH386 CM12.LOCAL server at c:\bkp\cm_pr1bkp. I will pick the cm_pr1bkp file and then restore the database on the new server as shown below.


Once you click OK on the dialog box as shown above it will present the following screen. Please make sure that you put a check box next to the backup as can be seen below and click OK. 


This should finish restoring your database on to the new SQL Server. Once complete, you will see the ConfigMgr database listed under the new server. As shown below: 


4. Verify the new SQL Server configuration on the new server. I used the same query which was used in the first step above:

select name, collation_name, user_access_desc, is_read_only, state_desc, is_trustworthy_on, is_broker_enabled,is_honor_broker_priority_on from sys.databases


Here you can see that my database didn’t retain the is_trustworthy_on and is_broker_enabled settings. Use the following queries to enable them:

--- Enable the SQL Broker on the Site database

USE master;


--- SET the Site Database as trustworthy

USE master;


--- SET the Database to honor the HONOR_BROKER_PRIORITY

USE master;

Now, run the following query to check the settings again

select name, collation_name, user_access_desc, is_read_only, state_desc, is_trustworthy_on, is_broker_enabled,is_honor_broker_priority_on from sys.databases where name = 'CM_PR1'


Now we can see that my database is ready for site maintenance.

5. Run Site maintenance.

Run the ConfigMgr setup from the start menu:


Then click Next on the “Before you Begin” page. Click Perform site maintenance or reset this Site on the Setup Wizard page as shown below:


Select Modify SQL Server configuration on the Configuration Manager Setup Wizard Site Maintenance page:


That will show you the old information about the SQL server. We will change that to our new SQL Server.


Now just let the ConfigMgr setup complete. After setup finishes, reboot the ConfigMgr site server and the SQL Server.  After the reboot, check the site settings in the ConfigMgr console and verify that it shows the new SQL Server listed. You might see a prompt stating that the site is in a Read Only mode; this can be ignored for now as we are running the re-synch.

Anurag Shukla | Support Engineer | Manageability - Microsoft Corporation

Get the latest System Center news on Facebook and Twitter:

clip_image001 clip_image002

System Center All Up:
System Center – Configuration Manager Support Team blog:
System Center – Data Protection Manager Team blog:
System Center – Orchestrator Support Team blog:
System Center – Operations Manager Team blog:
System Center – Service Manager Team blog:
System Center – Virtual Machine Manager Team blog:

Windows Intune:
WSUS Support Team blog:
The AD RMS blog:

App-V Team blog:
MED-V Team blog:
Server App-V Team blog:

The Forefront Endpoint Protection blog :
The Forefront Identity Manager blog :
The Forefront TMG blog:
The Forefront UAG blog:

Comments (21)
  1. yannara says:

    This certificate issue is important, and I have seen different instructions, which certificate is the one which needs to be exported to the cluster. I see, that ConfMgr server has its own SQL cert in personal store. Is that the one?

  2. rares says:

    Thanks.. I worked just fine.. How do I remove the old sql from the sccm?

  3. anurag says:

    I am not sure if you referring to removing it from the ConfigMg.if so then using the configmgr setup should take care of this.

  4. thomas says:

    i'm seeing this in the logs when i perform the site reset:

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted.~~  $$<Configuration Manager Setup><08-21-2013 12:55:22.716+300><thread=3208 (0xC88)>

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection  $$<Configuration Manager Setup><08-21-2013 12:55:22.723+300><thread=3208 (0xC88)>

    *** Failed to connect to the SQL Server, connection type: SMS ACCESS.  $$<Configuration Manager Setup><08-21-2013 12:55:22.730+300><thread=3208 (0xC88)>

    INFO: SQL Connection failed. Connection: SMS ACCESS, Type: Secure  $$<Configuration Manager Setup><08-21-2013 12:55:22.737+300><thread=3208 (0xC88)>

    1. Cristhian says:

      How do you solve it

  5. Patrik Enerstrand says:

    I have a solution on the Certificate / Trusted problem.

    go to the SCCM server open run and typ "MMC"

    in that console open file and add snappin.

    ADD certificate. in that wizard:

    "service account" next

    Local computer Next


    go to STS_SITE_COMPONENT_MANAGERtrusted pepole

    and take the domain certificate there an right click. all task> Export.

    copy the CRT file to the new SQL server and import that to local computer > trusted pepole.

    1. Hello Patrik,

      You have mentioned “find SMS_SITE_COMPONENT_SERVER finish” in my environment i dont see this.

      And can you please help me in steps involving in importing the same?

      Santhosh B S

  6. Maj says:

    it is 100% working, Many thanks and great efforts.

  7. Sergey says:

    Great article, but, this manual missed an important thing – you MUST create & configure SQL SSL certificate for the new SCCM DB server!
    Just create custom certificate for the new SQL server, import to the Personal Computer Certificates.
    And – on the certificate itself, right-click on the cert name, and select All Tasks -> “Manage Private Keys…”, then give the user the SQLSERVER service runs as Read permission in the security tab.
    After that select certificate from the SQL Server Network Configuration and click OK.

  8. Ronnie Calvin says:

    Problem in you system???
    Contact for FREE ANTIVIRUS TECH SUPPORT and get Expert Advise
    Contact on 1-800-935-0537

  9. Alex U says:

    Hello and thanks for your article. Question: is it really neccesary to restart the SQL server at the end? We need to move the DB From a local SCCM to a high available SQL (cluster) that holds another important production Databases. Thanks in advance for
    your response.

  10. Raj Sekhar Chatterjee says:

    Can you provide me step by step doc for SCCM 2012 installation & Configuration

  11. Gabino says:

    Thanks a lot!

  12. Gabino says:

    Thanks a lot!

  13. mangers says:

    After moving our primary site DB all of the DRS links to re-initialized before syncing again on their schedules. The Global groups were pretty quick but not the Site groups. We also learned how none of your Site replication groups will sync on their 5
    minute schedules until ALL of the Site groups have re-initialized, which can take a very long time in a large environment. I didn’t see this documented anywhere so wanted to mention it-

  14. Jim Gilbert says:

    Great Article! Thank you… All steps were very accurate.

  15. David says:

    Is supported to move to a SQL Server with version 2012 ? or we must before uprade sql 2008 to 2012 and move after ?

  16. SCADMIN says:


    This procedure can affects SCOM functionality?

  17. Alan Dooley says:

    Other things to consider…

    Site Server computer account must be an admin on your new SQL server

    If your SQL is clustered check which certificate is being using in SQL and make sure that your site server trusts it. You cannot see when in clustered SQL which certificate is being used, refer to registry key – HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate

    After restarting the site server it will take a long time for roles to be re-installed across all your systems, in larger environments where you may have tens of servers running CM roles this can take well over an hour, and that’s assuming that everything re-installs correctly first time.

  18. Greg says:

    This is a great article! i followed the same steps in a production environment with success. Looking at the installation log, I did see a bunch of errors about the certificate is not trusted by a trusted… That went on for about 15 minutes non-stop until the primary site actually self generated a certificate. Just make sure the account that you launched the process with has sysadmin rights, otherwise the install will stop in the middle of if. The only issue that I have is the reporting service role is still pointing to the old SQL box even afeer removing it and add it back.

Comments are closed.

Skip to main content