Backup System Databases Using Maintenance Plans
System databases are an integral part of SQL Server, as SQL Server is dependant on the System Databases for functionality. Database Administrators should make sure that all the system databases are part of their day to day backups. Most importantly, database administrators should take full backup of all the system databases immediately after they have applied any Hotfixes or Service Packs. Backups of system databases will let you restore and recover the SQL Server system in the event of system failure, such as the loss of hard disk, database corruption, hardware/operating system crashes or any natural disaster. As a DBA you need to make sure that the system databases are backed up regularly along with all the user databases and the backup tapes are stored in a secure location. To know more about system databases in SQL Server you can refer to my previous article titled “System Databases in SQL Server”.
Permissions Required to Create & Manage Maintenance Plans
Maintenance Plans are only displayed to those users who are connected to the SQL Server using Windows Authentication Mode. Moreover, you need to be a member of sysadmin fixed server role to create and manage maintenance plans. The maintenance plans on the SQL Server are visible only to those users who are member of sysadmin fixed server role.
Using Maintenance Plans to Backup System Databases
In this article we will be creating a Maintenance Plan using SQL Server Integration Services (SSIS) Designer Surface to backup all the system databases in SQL Server. To know more about different types of maintenance plan tasks that are available in SQL Server 2005 or SQL Server 2008 and their usage you can refer to my previous article titled “Overview of Maintenance Plans in SQL Server 2008”.
1. Connect to SQL Server 2008 Instance using SQL Server Management Studio.
2. In the Object Explorer, click on Management and right click Maintenance Plans to open up the popup windows as shown in the below snippet. In the popup windows you need to click on New Maintenance Plan….
3. In the New Maintenance Plan screen you need to provide the name of the Maintenance Plan. In this example the name for the Maintenance Plan is “BackupSystemDatabases’. Click OK to open up the SSIS Designer Surface as shown in the below snippet.
4. In the BackupSystemDatabases designer surface, you have the option to provide the description for the Maintenance Plan, Name & Description for Subplan, Manage Connections, and Configure Schedules.
5. Double click on Subplan_1 text which will open up Subplan Properties windows where you can provide the Name and Description about the Subplan as shown in the below snippet.
6. Next step will be to configure schedule for the maintenance plan which can be done by clicking calendar icon. In the Job Schedule Properties windows provide the details as shown in the below snippet and click OK to save the Job Schedule Properties window.