T-SQL Tuesday #85 – Backup and Recovery – Maintenance Plans

tsql2sday-300x300This month’s topic for T-SQL Tuesday #85 post is: Backup and Recovery. I think this is very interesting topic and will result in many new great articles. I assume you already know what a backup is, and why it is very important to do and store backups for all your databases. The question which many database administrators ask themselves is how to do backups right? What tools to use?

There is a lot of possibilities:

In my post for this month’s T-SQL Tuesday topic I decided to describe you, how you can leverage SQL Server built-in Maintenance Plans step-by-step.

Recovery Point Objective and Recovery Time Objective

In Business Continuity Planning) terminology, the Recovery Point Objective is the maximum targeted period in which you might lost data in case of a system failure. The Recovery Time Objective is an amount of time you need to recovery from a failure. Both are very important and you should know business requirements to set up your Backup Plan properly. If you know these things, then you can decide how often you need to take backups and how long you should store each of them.

Here is an example of matrix which describes backup’s type, schedule, and retention period which I use for some of my systems.

Backups types and schedules

Maintenance Plan

So… What is Maintenance Plan? It’s a ‘tool’ which was introduced in SQL Server 2005. Internally it is a SSIS package which can be easily created by wizard built into SQL Server Management Sudio (SSMS). You can create these packages to automate your most common maintenance tasks like: backups, consistency checks, index rebuilds, and logs cleanup.Maintenance Plan tasks

Each Maintenance Plan is composed of one or more subplans. Each subplan has its own name, description and schedule. I use subplans to distinguish various types of backups. Therefore I prefer to have one subplan for Full backups, one for Diff backups, and one for T-Log backups.

For each subplan the separate Agent Job will be created. That allows you to easily enable, disable, or test each of subplans.

One plan or many plans?

It completely depends on you and your environment. The most important thing is to standardize approach across all environments you are responsible for. If you prefer to have one plan with many sub-planes, that’s fine. If you prefer to have separate maintenance plan for each database, it’s fine as well. Key is to meet your standards. That helps later if you for example need to modify something. I prefer to have one maintenance plan for all system databases (master, model, msdb) and separate plans for each user’s databases.

Maintenance plan for system databases (step-by-step)
  1. To create a Maintenance Plan you need to start SSMS and connect to your target SQL Server instance. Then in Object Explorer expand [Management] folder and right-click on [Maintenance Plans]. In drop down menu click on [New Maintenance Plan…].Create new Maintenance Plan
  2. Provide Maintenance Plan name “MaintenancePlan – Backup – System databases”:Maintenance Plan name
  3. Set Maintenance Plan description, subplan name and description as per example below:Maintenance Plan subplans
  4. Set Schedule to execute backup every one day:Maintenance Plan schedule
  5. From Toolbox add new Back Up Database Task and setup as per example below:Maintenance Plan - general tab
  6. Enter Folder name, select “Create a sub-directory for each database” and set “Backup file extension”:Maintenance Plan - destination tabI prefer to use different backup file extension for different types of backups. That helps me later identify which files contain full backups and which differential ones. I use such extensions: *.full, *.diff, and *.trn.
  7. On the Options tab, selection option to compress backup and select “Verify backup integrity” checkbox:Maintenance Plan - options tab
  8. From Toolbox add new Maintenance Cleanup Task to cleanup old backup files. Specify the same folder as in point 6 above. Select “Include first-level subfolders” checkbox. Enter desired file age:Maintenance Cleanup task
  9. Take note of default location of Maintenance Plan report files using the icon indicated below:Maintenance Plan reports
  10. Take note of default folder:Maintenance Plan - reporting and logging
  11. From Toolbox add another Maintenance Cleanup Task. Select “Maintenance Plan text reports” option. Specify location obtained in point 10 above. Enter “txt” as file extension as below. Set the file age (4 weeks as default):Maintenance Cleanup task
  12. From Toolbox add History Cleanup Task. Select option to remove “Maintenance plan history”. Set default file age as 4 weeks:Maintenance Plan - history cleanup task
  13. Connect all tasks as indicated below:Maintenance Plan - connected tasks
  14. Maintenance Plan for backup of system databases is completed. Save the changes.
  15. Test created Maintenance Plan by executing corresponding SQL Server Agent job.
Maintenance plan for user’s database (step-by-step)
  1. Create a new Maintenance plan and name it “Maintenance Plan – Backup – AdventureWorks”.Maintenance Plan name
  2. Create 3 subplans, set Maintenance Plan description, subplan names and descriptions as per example below:Maintenance Plan subplans
  3. Setup subplan named Full. Create schedule to backup databases once a week:Maintenance Plan schedule
  4. From Toolbox add new Back Up Database Task. Select desired databases:Maintenance Plan - general tab
  5. Setup destination folder in a root of backup folder including Maintenance Plan suffix (“AdventureWorks” in this case) as the lowest level in folders hierarchy. Enter “full” as file extension:Maintenance Plan - destination tab
  6. Setup compression and backup integrity verification on Option tab.Maintenance Plan - options tab
  7. From Toolbox add new Maintenance Cleanup Task and set values as indicated below:Maintenance Cleanup task
  8. Connect both tasks as below:Maintenance Plan - connected tasks
  9. Setup Diff subplan. Create schedule to backup databases every day (except Sunday):Maintenance Plan schedule
  10. From Toolbox add new Back Up Database Task and configure as below. Select the same databases as in Full subplan. Set backup type as differential. Set file extension as “diff”. Select options for backup compression and integrity verification:Maintenance Plan - destination tab
  11. From Toolbox add new Maintenance Cleanup Task and setup as below:Maintenance Cleanup task
  12. Connect both tasks as below:Maintenance Plan - connected tasks
  13. Setup Trn subplan. Create schedule to backup databases every 15 minutes.Maintenance Plan schedule
  14. From Toolbox add new Back Up Database Task and configure as below. Select the same databases as in Full and Diff subplans. Set backup type as transaction log. Select options for backup compression and integrity verification. Set file extension as “trn”:Maintenance Plan - destination tab
  15. From Toolbox add new Maintenance Cleanup Task and configure as below:Maintenance Cleanup task
  16. Connect both tasks as below:Maintenance Plan - connected tasks
  17. Maintenance Plan for AdventureWorks2014 database is completed. Save changes.
  18. Test created Maintenance Plan by executing corresponding SQL Server Agent jobs staring from Full, then Diff and finally Trn backup types.Maintenance Plan - Agent Jobs
My advices
  1. Always set backup compression option in Back Up Database Task. Even in situation when you have compression enabled on server level. This will always compress your backups even in situation when somebody will change your server level setting.
  2. For each maintenance plan you create you should select a different directory in your root backup directory. That will help you keep your backups organised.
  3. If you create Maintenance Plan for only one database you don’t have to select “Create a sub-directory for each database” checkbox. If you create Maintenance Plan for more than one database this checkbox has to be selected.
  4. Remember to set up notifications about failed backups.
  5. Do backups! Always!

-Marek

Share it:
FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail

Leave a Reply

Your email address will not be published. Required fields are marked *

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close