Backup System Databases Using Maintenance Plans

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.