Maintenance Plan to Backup the Database
- To Create a Backup maintenance plan, Right click on Maintenance Plans folder and give a name to the plan:
- Drag and drop the “Backup Database Task” and double click on it:
- Select the Database which you want to maintain. Multiple databases can be selected:
- Specify the Backup type and the folder where the backup file is to be stored:
- Full or Differential or Transaction log back ups can be taken.
If it is a Database backup then backup file extension should be “bak” without any trailing dots.
For transactional log the extension should be “tran”:
Click on OK once everything is done.
Scheduling
To Schedule the plan, click on the button appearing near the schedule in main window. The scheduling window will appear.
Give a name for the schedule and its frequency. Frequency can be monthly, weekly or daily:
Report and Logging
Next, The report and logging option should be specified. Click on Logging option in the main maintenance task window. It can be specified if a new log file should be created for every maintenance run or append to the existing one.
Mail can also be sent to a recipient:
Maintenance Clean Up Task
The logs that are created in backup tasks can be cleaned up by this task. Drag and drop “Maintenance Clean Up task” and double click on it. The folder from where to delete all the backups should be mentioned. If it’s a DB backup then mention “bak” else if it is a transaction log back up to be deleted then mention “tran”. If both types of backups are to be cleaned up then specify “*” in the file extension.
Specify the age of files that are to be deleted:
The clean up maintenance task should execute only after backup has been taken. So the “clean up maintenance task” should be linked to “Back up Database task”:
Click on the Green link line to specify if the next task whether it should be executed on successful completion of previous task or on failure.
Manual Execution
Right click on the Test plan under Maintenance Plans and execute it:
No comments:
Post a Comment