Create Automated Maintenance Jobs with SQL Server Maintenance Plan

by Manzoor Siddiqui · Published July 4, 2015 · Updated September 24, 2015

SQL Server maintenance plan is a very useful and simple way to create automated maintenance plans to optimise and backup your database regularly. You can perform database maintenance and optimised your database by creating simple workflow of tasks with graphical user interface in SSMS. We are going to create a maintenance plan for full database backup and we will set the schedule in design window. So if you are database administrator or developer who wants to maintain and optimise database in quick way then here is a tool for you.

To create maintenance plan go to Object Explorer –> Management –> Maintenance Plans –> right click and select New Maintenance Plan…

It will prompt to provide maintenance plan name, put the name and click OK. Design window will be open and on left side you will get Toolbox. If by default toolbox is not available, you can select the same by pressing Ctrl+Alt+X or select from View menu.

Toolbox provides different useful maintenance plan tasks as given below:

– Backup Database Task

– Check Database Integrity Task

– Execute SQL Server Agent Job Task

– Execute T-SQL Statement Task

– History Cleanup Task

– Maintenance Cleanup Task

– Notify Operator Task

– Rebuild Index Task

– Reorganise Index Task

– Shrink Database Task

– Update Statistics Task

Now here we have to create Backup Database Task so drag and drop the task to design window from toolbox. Now right click on Backup Database Task and select Edit… It will open Backup Database Task where you have to provide necessary details. Select your database connection, backup type and name of database to backup then select the database backup path from Folder option. Select the Folder path and click OK.

On backup database task window you also have options like copy-only backup and you can also specify backup set expiry date. Then you can choose to backup database to either disk or tape as per your requirements. You can also divide the database backup into one or more files. You can choose verify backup integrity and set backup compression option if you want to save the disk space. Now click OK button and you will be back to design window. Now select Schedule option from design window and set the required schedule and click OK.

Now click on Save button from SQL Server Management Studio, which will create automated job and plan for your task. Once it is saved you can verify the job in jobs node and also plan is created under maintenance plans. So this is very simple and effective way to create a maintenance plans for your database servers.