SCCM diet

Online notes for reference

Replicating SCCM DB and adding replicated DB to SCCM Reporting – Part 2

In Part 1 I backed up the SCCM database and copied the backed up data base files to a different SQL server. I also attached the data base files and created the database

Part 2

In part 2 I am going to create a SQL job which will automate this task to run daily .This job is scheduled a time later than SCCM Backup time so that the latest backup files are present when job runs.

Open SQL management studio on server (On the replicated DB server) SQL01 in this case.

Go to SQL server agent , right click and New Job

DBRepl11

On the general menu provide the name of SQL job

 

DBRepl12

Click on Steps and and click on New

DBRepl13

First step will be close the connections to SQL server .

Add the following command to put the database in single user mode and click OK

ALTER DATABASE CM_R01 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBRepl14

Next Step is to detach DB . This step is needed because we need to copy the DB files from SCCM server and overwrite them. If you do not detach the database files will not be overwritten

SP_DETACH_DB ‘CM_R01’, ‘TRUE’

(Change the name of the data base in the above command)

DBRepl15

Next step is to copy the files from SCCM primary server to database server using the script as described in part 1

“c:\dbcopy\dbcopy.ps1”

DBRepl16

Once the data base files are copied next step is to attach the database to SQL server

CREATE DATABASE CM_R01
ON(FILENAME = ‘C:\DBCopy\CM_R01.mdf’),
(FILENAME = ‘C:\DBCopy\CM_R01_log.ldf’)
FOR ATTACH;
GO

Click OK

DBRepl17

Once all the steps in the job are complete. Next step is to schedule this job to occur daily sometime after the SCCM backups finish.

Since SCCM backups in my lab are scheduled to run at 1:00 AM everyday I have scheduled the job to run everyday at 4:00 AM to give enough time to backup to finish.

DBRepl18

Your job will appear under SQL Server agent – Jobs

DBRepl19

If you want to manually run the job to test if it runs correctly , Right click the job and select start the job at step 1

DBRepl20

You can always check the job history for troubleshooting purposes by right clicking the job and clicking on view history

DBRepl21

By expanding the step , each step is detailed. If there are any failure those will be recorded here.

DBRepl22

 

This concludes part 2

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: