How to Backup the whole SQL after CM Backup

The most of my Customers needs to Backup not only the CM_<Sitecode> Database to Backup, this Database is Backuped by the “Maintenance Tasks” > “Backup” in Config Manager.

But how we can full Backup the Other Databases that a Config Manager installation needs?
There are some other Databases like Reporting Services or WSUS DB’s in it.

(In this Way the SQL and ConfigManager is installed on the Same Server.)

Let’s have a Look for a simple Way, in most of all SQL-DBA’s eyes this way is crappy, but it works and is easy to implement:

Start the Microsoft SQL Server Management Studio and Brows via “Management” to the “Maintenance Plans Wizard” run it

In case of this Message

You can run this Command to enable the ‘Agent XPs’:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Thanks in this case goes to Martin Smith from http://stackoverflow.com/questions/3184286/how-to-create-maintenance-plan-in-sql-server

After That you can start the Maintenance Plan Wizard again, first Window say next then give them a Name Like CM_BackupFull, set no schedule

Select the “Back Up Database (Full)” next, next

Select the Databases to Backup:

Set the path to the Location

In the next Window you can generate a Report for your control or maybe send a Mail, next and finish

and you have a new Plan

After that be shure that the SQL-Server-Agent is started and running.

With the Execute Button in the right click you can test the Backup Manualy, but we like the automatisation that’s also why we use Config Manager

If your Test was successfully go to SQL-Server-Agent > JOB’s:

Copy the full name of the Backup Job, i this example it calls ‘CM_BackupFull.Subplan_1’

The next Step is to create a Bat File that name is “AfterBackup.bat” and create this file in your Config Manager inboxe like “<install path to>\Microsoft Configuration Manager\inboxes\smsbkup.box”

In this file set just the sqlcmd commad:

sqlcmd -S. -E -iE:\SCCMBackup\CM_BackupFull.sql

You see there is another File in this Command Line, let’s go to create the SQL Command in the Backup Folder “E:\SCCMBackup\CM_BackupFull.sql”

In this SQL Script File write the command, with your JOB Name:

msdb.dbo.sp_start_job @job_name=N'CM_BackupFull.Subplan_1'

That’s it, now we can  test our new Full Backup of SQL Databases:

Go to controlle the Backup is enabled and configured in Config Manager:

Then we open the “Configuration Manager Service Manager” and start the “SMS_SITE_BACKUP” component

Test this in your Lab befor use it in Productive, the Post is set “as is”

Have fun