SCCM Tutorial Backing up sql server database and preparing new sql server 2012

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:

ConfigMgr 2012 database
ConfigMgr 2012 database

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.

Also read:
– VM LAB Setup Guide Download

– How Policy Works in System Center Configuration Manager?

– How to configure IIS Web Portal for ConfigMgr?

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.

backup sql server prepare new server 1 1

migrate sccm 2012 r2 to new server

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.

STEP 3:

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:

 sccm 2012 database backup

sccm 2012 database backup

 

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.

Enable CLR integration

sp_configure’show advanced options’,1;

GO

RECONFIGURE;

GO

sp_configure ‘clr enabled’, 1;

GO

RECONFIGURE;

GO

Add SCCM Site Servers and SCCM Admin to SQL sys admin and local admin group

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:

restore sccm database from backup
restore sccm database from backup

 

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

move sccm 2012 r2 to new server

3. Provide the backup file that was created in the previous step. I had already copied the backup file to theWINDOWS-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:

 move sccm 2012 r2 to new server

move sccm 2012 r2 to new server

 

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.

move sccm database to another drive
move sccm database to another drive

 

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

SQL Server Backup Recommendations for Configuration Manager 2012
SQL Server Backup Recommendations for Configuration Manager 2012

 

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;
GO
ALTER DATABASE CM_PR1 SET ENABLE_BROKER
GO
— SET the Site Database as trustworthy
USE master;
GO

ALTER DATABASE CM_PR1 SET TRUSTWORTHY ON
GO
— SET the Database to honor the HONOR_BROKER_PRIORITY
USE master;
GO
ALTER DATABASE CM_PR1 SET HONOR_BROKER_PRIORITY ON;
GO
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’

 Backing up sql server database and preparing new sql server 2012

Backing up sql server database and preparing new sql server 2012

 

Hope this will be helpful.

Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

>