articles >> general dba >> Backup System Databases Using Maintenance Plans ...

Backup System Databases Using Maintenance Plans

By : Ashish Kumar Mehta Nov 17, 2008

Page 2 / 3


 

7. Next step will be to create a connection to SQL Server where the Maintenance Plan needs to execute. This can be done by clicking on Manage Connections…. and from the popup you need to select Add new connection…. which will indeed open up Connection Properties window. In the Connection Properties window you need to provide Connection Name, Server Name and choose appropriate authentication mode. To save the connection properties click OK.

 

8. Next step will be to drag and drop Back up Database Task, History Cleanup Task and Maintenance Cleanup Task on the Designer Surface to configure Maintenance Plan. 

 

9. Double click on Backup Database Task or right click and select Edit…. Option to configure the Backup Database Task. Provide Connection details, Select Backup type as Full from the dropdownlist, For Databases select All system databases from the dropdownlist (if you have reporting service and replication installed and configured then you need to choose reporting services and replication related distribution databases using the specify choose database option), provide the database backup location. If you are interested in verifying backup integrity then you can select the Verify backup integrity option. In SQL Server 2008, Microsoft introduced much awaited inbuilt database backup compression feature. If you want system databases to be backed up using backup compression feature then you can choose “Compressed Backup” option in the dropdownlist. To know more about database backup compression feature of SQL Server 2008 you can refer to my previous article titled “How to configure and Use Database Backup Compression in SQL Server 2008”. Finally click OK to save the configuration changes done to Back Up Database Task.

 

10. Double click on History Cleanup Task to configure the task as shown in the below snippet. To remove historical data you need to provide the history retention time and click OK to save the configuration changes.

 

11. Double click on Maintenance Cleanup Task to open up the configuration screen and provide the database backup folder location and also provide the extension of the files which needs to be removed at the end of retention period.

 

12. Finally you need to connect the green arrow from the Back up Database Task to History Cleanup Task and in the next step you need to connect the green arrow from the History Cleanup Task to Maintenance Cleanup Task to complete Maintenance Plan configuration.