Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Maintenance Plans and Backup File Management


By:   |   Last Updated: 2006-10-25   |   Comments (1)   |   Related Tips: More > Maintenance

Problem
Database maintenance is vital to the health of an organization's data delivery infrastructure. In SQL Server 2005 Microsoft redesigned the method through which Database Maintenance Plans are created and managed. These plans can be created through a wizard in SQL Server Management Studio (SSMS). They are handled mainly through the Business Intelligence Studio (BIDS), and custom plans can be created in BIDS using SSIS. Although the process is comprehensive, the management of physical backup files is not automatically handled in the Database Maintenance Plan Wizard, but history cleanup is completed. In this tip we will outline a number of methods that are available to manage these files.

Solution
Let's first talk about the process involved in creating a Database Maintenance Plan. You create maintenance plans in BIDS, just as you would create any other SSIS package. Here are the maintenance tasks available in SSIS:

Maintenance Tasks available through SSIS

Of particular interest are the two tasks highlighted above-the History Cleanup Task and Maintenance Cleanup Task. The History Cleanup Task deletes maintenance information generated prior to a specified date in the msdb database. One new and much appreciated feature in the tasks themselves is that you can view the T-SQL statement to be issued when the task is executed. Here is the History Cleanup Task and associated T-SQL statement:

The History Cleanup Task

T-SQL statement for the History Cleanup Task

The Maintenance Cleanup Task is a way of deleting physical backup files, although this task can be used to clean up other files as well. Here is what the Maintenance Cleanup Task looks like, along with its T-SQL statement:

The Maintenance Cleanup Task

T-SQL statement for the Maintenance Cleanup Task

You can customize each of these tasks by using an Expression or use the T-SQL statement as a scheduled job per se'. One thing to note about the Maintenance Cleanup Task is that it is not added to a maintenance plan when using the Database Maintenance Plan Wizard in SSMS. To add this task to an existing maintenance plan, right-click the plan in SSMS and choose Modify:

Modifying an existing Maintenance Plan

A screen opens in SSMS giving a view similar to the SSIS Designer and listing the steps involved in the plan:

View of Maintenance Plan in SSMS

You can add additional steps to the plan by dragging and dropping a task over to the screen. In this case we'll add a Maintenance Cleanup Task to it:

Adding a Maintenance Cleanup Task to a maintenance plan

Once you add the task to the plan you have to add more information: the server connection, the folder where the backup files are housed, the file extension you want it to delete, and time span. In addition you must add a Precedence Constraint to the task so that the order of task execution is maintained:

Additional information for the Maintenance Cleanup Task

There are other alternatives to deleting the physical backup files, but perhaps the safest is to run a vbscript on a daily basis. Here is a sample script that deletes files whose date last modified is over 30 days:

Sample VBScript for File Deletion

Next Steps



Last Updated: 2006-10-25


next webcast button


next tip button



About the author
MSSQLTips author Edgewood Solutions Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, February 26, 2015 - 2:34:09 PM - sree Back To Top

HI,

 

I am getting the below error while opening maintenance plan.

Please help on this.

 

 

The maintenance plan has been modified using a tool other than the maintenance plan designer. You must use SQL server integration services to modify this maintenance plan from now on.

 

Thanks,

Sree


Learn more about SQL Server tools