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

This 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:

• create own T-SQL scripts,
• use third-party scripts like Ola Hallengren solution,
• use PowerShell,
• create maintenance plans,
• … and probably a lot of more.

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.

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.

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…].
2. Provide Maintenance Plan name “MaintenancePlan – Backup – System databases”:
3. Set Maintenance Plan description, subplan name and description as per example below:
4. Set Schedule to execute backup every one day:
5. From Toolbox add new Back Up Database Task and setup as per example below:
6. Enter Folder name, select “Create a sub-directory for each database” and set “Backup file extension”:I 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:
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:
9. Take note of default location of Maintenance Plan report files using the icon indicated below:
10. Take note of default folder:
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):
12. From Toolbox add History Cleanup Task. Select option to remove “Maintenance plan history”. Set default file age as 4 weeks:
13. Connect all tasks as indicated below:
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”.
2. Create 3 subplans, set Maintenance Plan description, subplan names and descriptions as per example below:
3. Setup subplan named Full. Create schedule to backup databases once a week:
4. From Toolbox add new Back Up Database Task. Select desired databases:
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:
6. Setup compression and backup integrity verification on Option tab.
7. From Toolbox add new Maintenance Cleanup Task and set values as indicated below:
8. Connect both tasks as below:
9. Setup Diff subplan. Create schedule to backup databases every day (except Sunday):
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:
11. From Toolbox add new Maintenance Cleanup Task and setup as below:
12. Connect both tasks as below:
13. Setup Trn subplan. Create schedule to backup databases every 15 minutes.
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”:
15. From Toolbox add new Maintenance Cleanup Task and configure as below:
16. Connect both tasks as below:
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.

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: