Search: Advanced search
Browse by category:
Schedule MSSQL backup in 2005 with maintenance clean up task
Schedule backup and cleanup task in SQL server 2005
To schedule a backup of databases in SQL server 2005 we need to create a Maintenance plan task.we have to do the following
1) Select "Maintenance Plan" from the "Management" section in the Object explorer window.
2) Right click on the Maintenance plan and select "New maintenance plan" .
A Maintenance plan design panel will appear in the right and a toolbox with Maintenance plan tasks
will appear in the left.
3) Now drag the "Backup a database task" from the toolbox to the design panel.
4) Double click on the task from the design panel to edit the task.
Enter the connection type used to connect to the database.
Select the back up type ,We can choose from the following three options
Full, Differential, transaction log.
Select the option Full to perform a complete backup of the databases.
Choose the option "Create a back up for every database". Select the folder to which the backup the shall be made.
Enter the Backup file extension as bak . Click ok .
Unlike SQL Server 2000, there is no option in the Back up database task for removing database backup files from the backup folder. In order to delete obsolete backup files, an additional Maintenance Cleanup Task must be inserted and configured. To do that we need to
1) Drag and drop "Maintenance Clean up task" to the Design panel.
2) Double click on the "Maintenance plan task" from the design panel to edit it.
Enter the the connection type as you did in the "back up database task".
Check "Backup files".
Specify the folder ,to which the back up was made.
Specify the File extension, in our its bak .
Check "Delete files based on the age of the file at task run time" and set the Delete files older than the following option.
Click ok .
The Maintenance Cleanup Task should be executed only upon successful completion of the Back up database task. To ensure this, we have to link both tasks with a constraint.
Click on the first task and then drag an available component output (depicted as a green arrow) to the next task.
Double-click on the arrow to check and edit, if necessary, the Precedence Constraint properties:
Evaluation operation: Constraint.
Multiple constrains: By default, it is Logical AND. In this case, it does not matter because we only have one constraint.
Click on the Save all to save the Maintenance plan.
Powered by Deru Communications (Webhosting Knowledgebase)