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
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
On the general menu provide the name of SQL job
Click on Steps and and click on New
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
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)
Next step is to copy the files from SCCM primary server to database server using the script as described in part 1
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’)
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.
Your job will appear under SQL Server agent – Jobs
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
You can always check the job history for troubleshooting purposes by right clicking the job and clicking on view history
By expanding the step , each step is detailed. If there are any failure those will be recorded here.
This concludes part 2