13. You could see that Maintenance Plan is currently configured to take full backup of master, msdb and model databases. However, in SQL Server 2005 and higher versions there is a new hidden read only system database named Resource database which was introduced by Microsoft. To know more about Resource Database you can refer to my previous article titled “Importance of the Resource Database”. Resource database basically contains copies of all system objects that are shipped with SQL Server 2005 and SQL Server 2008. In order to take the backup of Resource Database you need to perform a file based or a disk based backup of mssqlsystemresource.mdf and mssqlsystemresource.ldf files, by treating the files as if they were like any other binary files, instead of a database file.
14. In Maintenance Plan as there is no other task available which performs file copy of Resource Database (MDF & LDF Files), the only option available with us is to add a new step manually within the SQL Server Agent Job “BackupSystemDatabases.BackupSystemDatabases” which was created by the Maintenance Plan. The command used for copying mssqlsystemresource.mdf and mssqlsystemresource.ldf files is mentioned below.
Syntax
XCOPY “D:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLBinnmssqlsystemresource.*” “D:DatabaseBackups” /Y
15. Before saving the SQL Server Agent Job you need to make sure that the jobs success and failure criteria for each steps are modified and it resembles as shown in the above snippet.
Executing the Newly Created Maintenance Plan
Once the “Basic Maintenance Plan for AdventureWorks databases” is created successfully you can execute the same either by right clicking the plan and then clicking Execute under the Maintenance Plans. Or you can expand the Jobs under SQL Server Agent node and execute the following SQL Server Agent Job BackupSystemDatabases.BackupSystemDatabases.
Conclusion
Database Administrators should make sure that all the system databases are part of the day to day backups. If you have applied any Hotfixes or Service Packs then immediately you should take a backup of all the system databases including the hidden read only Resource Database. If you have proper backups of system databases then you will be able to restore and recover a SQL Server system in the event of system failure. In this article we have seen how to leverage Maintenance Plans to take a backup of all the system databases.
]]>