• Home  / 
  • Research  / 

Moving the ConfigMgr Current Branch database to another server (as in back to the primary site server)

For the tenth time or so this year, I was tasked with moving a ConfigMgr database away from a remote SQL server to where it’s supposed to be: At home! E.g. on the same virtual machine as the primary site server. Why you ask? Because it’s better 🙂

This article is based on a great blog post by Anurag Shukla (Microsoft): https://blogs.technet.microsoft.com/configurationmgr/2013/04/02/how-to-move-the-configmgr-2012-site-database-to-a-new-sql-server but I have added in workarounds/configurations for some real world issues you are likely to run into.

WARNING: Don’t expect this process to work fine if you have a CAS, I recommend this for standalone Primary Sites only. If you have a CAS you have problems enough already, fix them first, like migrating off the CAS hierarchy into a standalone Primary Site.


In this guide I have a remote SQL Server named SQL01, it’s running SQL Server 2012 SP3, and it’s currently hosting a ConfigMgr Current Branch 1606 Site Database named CM_PS1. In this guide the database will be moved from SQL01 to the Primary Site Server which is CM01. The overview steps are the following:

  • Stop the ConfigMgr services on CM01, and backup the database on SQL01
  • Review the current database settings on SQL01
  • Install SQL Server (same version) on CM01
  • Restore the database on CM01
  • Setup ConfigMgr to use the database on CM01

Step 1 – Stop ConfigMgr services on CM01, and backup the site server database on SQL01

In this step you backup the ConfigMgr Site Server database on SQL01, and verify some database settings.
1. On CM01, stop most ConfigMgr services by running preinst.exe /stopsite. You find the preinst.exe file in the ConfigMgr installation directory (E:\Program Files\Microsoft Configuration Manager\bin\X64\00000409 in my environment).

Stopping most of the site components and services.

2. Using PowerShell, stop the remaining ConfigMgr services by running the following commands in an elevated PowerShell using the following commands:

Stop-Service -Name AI_UPDATE_SERVICE_POINT      

Note: The AI_UPDATE_SERVICE_POINT service is only available if you configured Asset Intelligence.

3. Then use the following commands to make sure they are stopped

Get-Service -Name AI_UPDATE_SERVICE_POINT      

4. On SQL01, using SQL Server Management Studio, make a full backup of the ConfigMgr database. In my case the database was named CM_PS1.

Note: If you want to go all in with PowerShell, you can also use the dbatools from Microsoft MVP Chrissy LeMaire (@cl). Here is the link: https://dbatools.io 

Backing up the database using SQL Server Management Studio,

Review the current database settings on SQL01

At this point, it’s time to review the current SQL Server and Site Server database settings on SQL01.

SQL version can be verified by running the following command on an elevated PowerShell prompt on the SQL Server.

Invoke-Sqlcmd -Query "SELECT @@version" | Format-Table –Wrap -AutoSize
Checking SQL version.

Server Collation can be verified by running the following command on an elevated PowerShell prompt on the SQL Server.

Invoke-Sqlcmd -Query "SELECT CONVERT (varchar, SERVERPROPERTY('collation'));"
Checking Server Collation.

CLR Integration can be verified by running the following command on an elevated PowerShell prompt on the SQL Server. A run_value of 1 means its enabled.

Invoke-Sqlcmd -Query "sp_configure 'clr enabled'"
Checking if CLR Integration is enabled.

You also need to verify the database configurations for the CM_PS1 database, and even to this absolutely can be done in PowerShell too via invoke-Sqlcmd, I ran this query in SQL Server Management Studio since it’s easier to view there.

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
Verifying the configuration of the CM_PS1 database.

Step 3 – Install SQL Server (same version) on CM01

Technically it is supposed to work restoring the database to a higher version, but for safety I’ve always restored to the same version, and once verifying everything works, then upgraded to a later version if needed. Also make sure you to install SQL with the same Server Collation.

1. Install the same version of SQL on CM01, in my example SQL Server 2012 SP3 with GDR update (version 11.0.6248.0).

2. After install, verify if CLR Integration is enabled by running the following command on an elevated PowerShell prompt.

Invoke-Sqlcmd -Query "sp_configure 'clr enabled'"

3. If CLR Integration is enabled (run_value is 1), continue to the next step, otherwise enable it by running the following query in SQL Server Management Studio. I had to do that in my environment.

sp_configure 'clr enabled', 1      

Step 4 – Restore the ConfigMgr Site Database on CM01

Now it’s time to restore the database.

1. Copy the SQL backup from SQL01 to CM01.

2. Using SQL Server Management Studio, restore the previous backup.

3. After restoring the backup, review the database configuration by running the following query:

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
Verifying configuration of the CM_PS1 database.

As you can, some of the CM_PS1 database settings were not restored, so they needed to enabled by running the following SQL commands:

USE master      

To verify the configuration, I once again ran the query:

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

Yay, now the database settings are what they are supposed to be.

The updated settings for the CM_PS1 database.

Step 5 – Setup ConfigMgr to use the database on CM01

The final step is to setup ConfigMgr to use the database on CM01, and for that you run the ConfigMgr setup in maintenance mode.

For ConfigMgr Current Branch you no longer find the setup on the start menu, like you had for ConfigMgr 2012, but rather in the cd.latest folder in the installation directory (E:\Program Files\Microsoft Configuration Manager in my environment).

1. On CM01, navigate to the ConfigMgr installation directory, and in the cd.latest\smssetup\bin\x64 folder, run setup.exe.

2. On the Available Setup Option page, select the Perform site maintenance or reset this site option, and click Next.

The Available Setup Options page.

3. On the Site Maintenance page, select the Modify SQL Server configuration option, and click Next.

The Site Maintenance page.

4. On the Database Information page, type in the Site Server (also the SQL Server) name, CM01.corp.viamonstra.com in my environment, and click Next.

The Database Information page.

5. Complete the wizard.

Real world note: Don’t be surprised if it fails. ConfigMgr Current Branch Setup (and older versions too) seems extremely picky about the SQL Server Native Client, and will throw the following error if it doesn’t like it:

This primary site is corrupted. Setup cannot find a valid site control image in the database or in the site control file.

If you open the C:\ConfigMgrSetup.log you will find additional info like:

SQL Server error: [08S01][233][Microsoft][SQL Server Native Client 11.0]Shared Memory Provider: No process is on the other end of the pipe.
*** [08S01][233][Microsoft][SQL Server Native Client 11.0]Communication link failure
*** Failed to connect to the SQL Server.   

But no worries, there is a fix for that 🙂 (keep on reading)

ConfigMgr Setup being unhappy about the SQL Server Native Client.

The fix? Simply run the setup wizard one more time, with the same settings.

6. When setup is completed, take a coffee break for about ten minutes, and then reboot both the ConfigMgr site server and the SQL Server.

7. After the reboot of both servers, have another coffee break, and then check the Site Server properties. They should now show Site Server (CM01 in my case), as the SQL Server.

Site Server properties, showing CM01 as the SQL Server.

8. Leave both servers running for a day or so, and after verifying that ConfigMgr has removed the Component server role from SQL01, you can delete the SQL01 server from ConfigMgr (assuming it doesn’t have any other roles).  Wait another hour and then turn off the original SQL Server (SQL01).

9. Yay, you now have your Site Server using a local SQL. Very shiny!

The ConfigMgr console now showing only CM01.

Extra Resources

For info on how to migrate related database like the WSUS DB or Reporting Services (migrating reporting services is usually not needed, just migrate any custom reports to the new server). Check the below links for more info:

Migrating SQL Reporting Services to a new server by moving the Reporting Services databases

Migrate WSUS to a new server

This post is also useful if you just want to change from WID to SQL

Migrating the WSUS Database from WID to SQL

Written by Johan Arwidmark.

About the author

Johan Arwidmark

5 5 votes
Article Rating
Notify of
Oldest Most Voted
Inline Feedbacks
View all comments
1 year ago

Hi Did any one faced an iissue with PAckages, Drivers and Task seqeunces are not visible in the console after DB migration ?
The devices and Applications are fine, just the packages not.
The strange part is that the Task Sequences and Software distribution with packages works fine we are able to make new builds, but Task sequences and packages are not visible in SCCM console.

1 year ago

If you can't get rid of a CAS. Have you ever moved the database from a remote server to the CAS?

2 years ago

We've got a situation where SQL isn't in a good way on the Primary Server… Would we be able to stop ConfigMgr, detach the DB's, uninstall SQL, re-install SQL, then re-attach the DB's and stuff will just work… or will we still need to complete a full backup/restore & run the Modify SQL connection steps?

1 year ago

Sorry to comment on this old post… But we're finally getting some time to tackle addressing this one again… Running through this process on an offline copy of our SCCM server & when doing the Modify SQL connections step, we're getting the following error "ConfigureSQLAlias() failed to connect to the registry with error 53".
Is this anything to worry about or possibly just caused because the system is offline (or already had SQL on the same machine previously?)

2 years ago

This is a very cool manual, I did it for the first time and thanks to your instructions I was able to cope with it, thanks again you are a real specialist.

2 years ago

Any guides for migrating from an old site to a new site? Basically rebuilding MEMCM from scratch…not sure if two sites can exist in the same domain for migration duration

Chris C
Chris C
3 years ago

Hi, We are noobs so we assumed that moving the DB to our dedicated sql failover cluster would help the site server perform better. Should we abandon that idea and look at tuning up this single site server instead?

Chris Pinnelli
Chris Pinnelli
3 years ago

This is great but what about the SUSDB and ReportDB?

4 years ago

In case the Migration step is failed during the ConfigMgr setup in maintenance mode (Your Step 5), how to perform fallback? Just re-run the ConfigMgr but Modify SQL Server configuration with original settings?

4 years ago


We have a setup where the database resides on a external SQL server 2012 , and we have the reporting server setup on the site server on SQL 2014.
But the reporting database itself resides on the SQL cluster on SQL 2012.

How would you go about moving this to a SQL 2017 cluster?

Ashley Johnson
Ashley Johnson
4 years ago

Invoke-Sqlcmd -Query "SELECT CONVERT (varchar, SERRPROPERTY('collation'));" ->
Invoke-Sqlcmd -Query "SELECT CONVERT (varchar, SERVERPROPERTY('collation'));" FYI

Kade Porter
4 years ago

Hi Johan,

Great post! If I want to migrate my SQL DB from being locally installed on the Primary Site server to a remote SQL server, would the process be essentially the same?

1 year ago

Hello Johan! In this scenario where you move the database from the primary site to a new instance on an existing SQL server, don't you need to add the SQL server first as a Site System Server in MECM? Or is this done automatically during the MECM Setup Wizard when you modify the SQL Server configuration? Thanks!