SCCM SQL Server Database Migration | Part 2 |ConfigMgr| Step by Step

The site database server is one of the critical roles of SCCM architecture. SCCM site database is a server that runs a supported version of Microsoft SQL Server. SQL Server DB is the database used to store information for SCCM sites and clients. Let’s see SCCM SQL Server Database Migration.

Related Post New ConfigMgr Primary Server Installation Step by Step Guide | SCCM

Each SCCM site in an SCCM hierarchy contains a site database and a server that is assigned the site database server role. To know more about the supported versions of SQL Server for SCCM, you may check here.


I will be covering end to end SCCM DB migration topic in this post. For detailed steps on how to install a SQL Server 2017 and the industry best practice is described in my previous post.

Patch My PC

This is a continuation of my series of the post The Complete Guide for SCCM Server Migration.

  1. The Complete Guide for SCCM Server Migration Part 1 – SQL 2017  -SCCM SQL Server 2017 Installation and best practices
  2. The Complete Guide for SCCM Server Migration Part 2 – Database Migration
  1. The Complete Guide for SCCM Server Migration Part 3 – WSUS Server Migration
  2. The Complete Guide for SCCM Server Migration Part 4 – Primary Server Migration – SCCM Site Restore without SCCM Backup

Content of this post

- Pre-requisite required for migrating the SCCM DB from old server to new SQL Server 2017
- Steps involved in Migrating the database
- Post SCCM DB Migration checks

Pre-requisite for Migrating SCCM Database

  1. Reboot the Primary Site and new/old SQL Server to avoid any pending reboot instances.
  2. Capture the logins from old SQL server by running the script and store the output in a notepad. The user should be having the appropriate permission on the SQL to perform these actions(sysadmin preferred).
;WITH Logins AS (
SELECT PrincipalName
 , RoleName
 FROM sys.server_principals prn
 LEFT OUTER JOIN sys.server_role_members mem
 ON prn.principal_id = mem.member_principal_id
 LEFT OUTER JOIN sys.server_principals rol
 ON mem.role_principal_id = rol.principal_id
 WHERE prn.is_disabled = 0
 AND prn.type IN ('U','G')
SELECT N'USE [master];' AS CommandsToKeepAndRun
-- Logins
SELECT N'IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = N'''+PrincipalName+N''')
CREATE LOGIN ['+PrincipalName+N'] FROM WINDOWS WITH DEFAULT_DATABASE = ['+ISNULL(default_database_name,N'master')+N'];
 FROM Logins
-- Server Roles
 FROM sys.server_principals prn
 LEFT OUTER JOIN sys.server_role_members mem
 ON prn.principal_id = mem.member_principal_id
 LEFT OUTER JOIN sys.server_principals rol
 ON mem.role_principal_id = rol.principal_id
 WHERE = N'''+PrincipalName+N'''
) != N'''+RoleName+N'''
ALTER SERVER ROLE '+RoleName+N' ADD MEMBER ['+PrincipalName+N'];
 FROM Logins
-- Server Permissions Extended:
SELECT CASE prm.class
 WHEN 100 THEN prm.state_desc+' '+prm.permission_name+' TO [''];' COLLATE SQL_Latin1_General_CP1_CI_AS
 WHEN 105 THEN prm.state_desc+' '+prm.permission_name+' ON ENDPOINT :: ['+(SELECT name FROM sys.endpoints WHERE endpoint_id = prm.major_id)+'] TO [''];' COLLATE SQL_Latin1_General_CP1_CI_AS
 FROM sys.server_permissions prm
 INNER JOIN sys.server_principals pal
 ON prm.grantee_principal_id = pal.principal_id
 AND pal.is_disabled = 0
 AND pal.type IN ('U','G')
--Courtesy: Benjamin Reynolds--

3. Stop the SCCM services on Primary site and take the DB backup to avoid any data loss.

4. Backup SCCM DB

a. Initiate the backup by right-clicking the SCCM database and selecting the tasks and then Back up.

SCCM SQL Server Database Migration | Part 2 |ConfigMgr| Step by Step 1

b. Select the backup destination on Disk and add the backup the location to save the file. Mention the file name with .bak extension.

SCCM SQL Server Database Migration | Part 2 |ConfigMgr| Step by Step 2

c. Select media option on the left and select the checkboxes for Verify backup when finished and Perform checksum before writing to media

SCCM SQL Server Database Migration | Part 2 |ConfigMgr| Step by Step 3

d. Select OK to start the backup and wait for the backup to complete. You may check the backup progress on backup GUI or else run the below query to see the detailed progress.

Migrate sccm db
select command 
 ,CAST(total_elapsed_time/1000.0/60.0 AS NUMERIC(8,2)) AS [Elapsed Min] 
 ,CAST(estimated_completion_time/1000.0/60.0 AS NUMERIC(8,2)) AS [ETA Min] 
 ,CAST(estimated_completion_time/1000.0/60.0/60.0 AS NUMERIC(8,2)) AS [ETA Hours]
 ,CAST(percent_complete AS NUMERIC(8,2)) AS [Percent Complete] 
from sys.dm_exec_requests 
where session_id = (select max(spid) from master.dbo.sysprocesses with (nolock) where cmd in ('RESTORE DATABASE','BACKUP DATABASE'))

5. Once the backup is completed, stop the SQL Services on old SQL Server and copy the backup to the new server.

6. Add Primary site server as a member of Local Admin group of new SQL Server. It should be a direct member and not a member of any security group. This is must otherwise, DB move will fail.

Migrate sccm db

7. Restore Database on new SQL Server. I am using a SQL server 2017 on Windows Server 2016.

a. Initiate restore by right-clicking the databases and selecting Restore

Migrate sccm db

b. Select the backup location as device and add the backup location by navigating to the location where you have copied the backup from the old server to the new server.

Migrate sccm db
Migrate sccm db

c. Select the files tab and here you can see the previous file location and new file locations. There is no restriction that you have to use the same drive layout of old SQL server on the new one.

Migrate sccm db

d. Select OK, to initiate restore process. Check the restore progress on Restore GUI or the script provided above in 4.d.

Migrate sccm db

8. Enable CLR

EXECUTE sp_configure 'clr enabled', 1;

9. Restore the logins by running the script which was received as the output from step 2

10. Reconfigure the restored database where XXX is your site code.

a. Enable the SQL Broker on the Site database

USE master; 

b.  SET the Site Database as trustworthy

USE master; 

c. SET the Database to honor the HONOR_BROKER_PRIORITY

USE master; 

11. Verify the SQL Server configuration on the new server. Use the below query to check the database settings. Make sure is_trustworthy_on and is_broker_enabled settings is on for the SCCM database

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
Migrate sccm db

12. Start the SCCM Services on Primary site and we are ready to move the SCCM database now with all pre-requisite tasks completed.

 Move the SCCM Database

  1. On the primary site open the SCCM Setup Wizard from the server and proceed to The Getting started page.
Migrate sccm db

2. On The Getting Started page, select Perform Site Maintenance or reset this site and click next.

Migrate sccm db

3. On the Site Maintenance window, select Modify SQL Server Configuration and select Next.

Migrate sccm db

4. On Database Information Window, provide the new SQL Server FQDN and select Next.

Migrate sccm db

5. On Configuration windows, you can view the setup progress and also the detailed information can be visible in ConfigMgrSetup.log.

Migrate sccm db

Post SCCM DB move Checks

  1. If you have moved the SQL server of a primary site in a multi-tiered hierarchy then the site will be in maintenance mode (Recovering Deltas). The site will re-initialize the replication groups which needs to be updated. So you need to monitor the site replication closely.
  2. Verify the sitecomp.log for bootstrap operation post DB move. make sure you don’t reboot the server if the bootstrap operations are continuing.
  3. Verify the logs on site systems connecting to the database like MP, WSUS etc.
  4. Create a test package and distribute it to the test distribution points
  5. Plan to move the Reporting services from the old SQL server to new SQL Server along with the custom reports.
  6. Plan for moving the WSUS database from old server to new SQL server.

38 thoughts on “SCCM SQL Server Database Migration | Part 2 |ConfigMgr| Step by Step”

    • Once the SQL DB is moved, you need to install the reporting service role on new SQL Site System Server and export the custom reports from old server to import on the new one.

  1. Hey,

    I’m trying to migrate a 2008 RE backup (10.00.1600) onto a 2017 version (14.00.1000), however, once I try to redote the backup I get the following error message:

    System.Data.SqlClient.SqlError: The backup of the system database on the device (FILEPATH)\master.bak cannot be restored because it was created by a different version of the server (10.00.1600) than this server (14.00.1000). (Microsoft.SqlServer.SmoExtended)

    Do you have any idea what’s going on? Thank you.

  2. What would be your recommendation for the Stanalone SCCM infra?
    Our SCCM is is installed on the Windows 2012 R2 STD 64-bit and the DB in the same box SQL Server 2012 Standart Edition (SP4) which just entered the extended support.

    Recently we upgraded the ConfigMgr to CB 1806 and now planning to upgrade the ConfigMgr DB.

    What would be the recommended version to upgrade without moving the database to a new server?

    Many thanks in advance!

    • So if you are asking for the SQL Server upgrade then you can go for SQL Server 2016 SP2. But again the OS on the server is still Server 2012 which has moved to the extended support. So better is to upgrade OS and SQL in a phased manner.

  3. I have migrated the DB to a new SQL server, however, when i launch SCCM, i notice that no packages are available, no boot images, no driver packages, no task sequences and no operating system image… I then re-point the SCCM install back to the original SQL server and all of the missing packages etc are available

  4. When we do the migration, we run into the error:
    INFO: Creating SQL Server machine certificate for Server []…
    INFO: ” is a valid FQDN.
    ERROR: Failed to create SQL Server certificate on server
    ERROR: Failed to create SQL Server [] certificate remotely.

    The user we’re using for the installation is sysadmin on the SQL cluster, Full Administrator in SCCM and member of the local admin group of the cluster nodes of the new SQL cluster, the old SQL cluster and on the SCCM server.
    Any ideas, what could be wrong?

  5. Hello Rajul,

    Thanks for the great post first! Then I just wanted to know if and when you plan to write the 2 lasts parts of the collections?


  6. thanks for you really usefull article!
    one question: do we need to recreate the endpoint used by service broker in the destination server? or it is created by the SCCM Setup Wizard?

    • Setup wizard will take care of the endpoint login of the primary site. If you are having a CAS and secondary site connecting to the PS DB then, only the PS endpoint login will be recreated by the setup. Remaining will be take care by the login backup and import script.

      • thanks Rajul! i meant the endpoint node : this must be scripted out and executed on the destination instance? thanky very much!
        CREATE ENDPOINT [ConfigMgrEndpoint]
        , AUTHENTICATION = CERTIFICATE [ConfigMgrEndpointCert]

  7. What are your thoughts on moving from Enterprise Edition to Standard Edition using this process? Specifically moving from SQL Server 2012 SP4 Enterprise Edition to SQL Server 2016 SP2 Standard Edition.

    I am far from expert in SQL, but the only issue I’ve been able to identify (With the help of input from Garth via Technet) is if the DB us currently using any Enterprise features, especially partitioning.
    “SELECT * FROM sys.dm_db_persisted_sku_features;” returns nothing, and it appears that 2016 now supports partitioning since SP1 anyway.
    Any other roadblocks to changing version like this when moving the database?

  8. Thanks for your article.
    We have SCCM (Current Branch) and SSRS (on different servers) databases configured with 2 instances on SQL Fail over Cluster. Since we have some issue with backing up the FoC servers, we prepared SQL Always on Availability Groups. To migrate the DBs (SCCM & SSRS will use same instance), steps are same as you mentioned with your article? Please suggest

  9. I’ve run into the same issue as Bobby in that everything appears to be well but the package content is missing.

    I’ve completed all the instructions as per the guide.

    Just to add, the new server collation is set to Latin_genral_Cl_AS. The collation on the SCCM database on that server is correctly set to SQL_Latin_General_CP1_Cl_AS. Would this collation setup cause the initial issue I face and any others moving forward?

  10. Hi Rajul and Anop, Thank you for the documentation. Event though i followed up the same steps when i open console only administration tab is shown. I tried site reset which did not work. I also tried modify sql options that gave an error in configmgrsetup.log “failed to create sql alias on the server”. And also “”CTool::ConfigureSQLAlias() failed to connect to the Registry with error 53″” .Site server computer account is direct member of local administrators group on database server and it has sysadmin rights on the database server. Do you guys have any idea?


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.