Backup Database Task in SQL Server Maintenance Plans

By: Sergey Gigoyan | Updated: 2019-12-05 | Comments | Related: > Backup

Problem

Undoubtedly, having properly designed SQL Server database backup solutions is crucial for any company. Having discussed database Maintenance Plans in the previous two articles (here and here) from this series, in this article, we are going to explore how to implement database backup tasks using Maintenance Plans.

Solution

A complete backup solution will be described in this article by using the Maintenance Plan. Implementing Full, Differential, and Transaction Log backups will be illustrated. Additionally, the advantages of the Database Backup Task in the newer versions of SQL Server over the older versions will be explained.

It is assumed that the reader is aware of the different types of database backups and explaining the concepts of backup types is outside of this topic. However, in case of having limited knowledge or no understanding about the backup types, review this tutorial.

Implementing database backup solution using SQL Server Maintenance Plan Wizard

Designing a backup strategy depends on the company’s needs and policy. Thus, various companies can have various approaches to backing up their databases. In this article, we will illustrate the simple solution by designing a backup strategy for one database - TestDB.

It is assumed that having weekly full backups, daily differential backups and transaction log backups every ten minutes is enough for our case. Let’s assume we have created a folder to store these backups, TestDB_Backups, and there are three subfolders for each type of database backup – Full, Differential and TransactionLog:

Create SQL Server Maintenance Plan for Full Database Backups

Let’s start with designing the full backup task by using the Maintenance Plan Wizard:

After launching the wizard, we click "Next":

Then we choose a name for our task and write a short description:

After that, we click on "Change" to set a schedule:

We have set the task to run weekly (on Sundays) at 1 AM. After clicking "OK" we will return to the previous window and then we click "Next" to move forward:

On the page above, we choose the Back Up Database (Full) task and clicked "Next" to continue:

As we have only one task, we do not need to arrange the task order. Hence, we click "Next" again:

In the window above, we choose the database or databases which should be backed up. In our case, it is only TestDB database.

On the "Destination" tab, we set the backup file location. In our example, it is "D:\TestDB_Backups\Full":

On the "Options" tab, we can choose various options of backing up the database. Here, however, it is important to mention the differences between the new and older versions of SQL Server. In the pictures below, the left-hand side is the "Options" tab of SQL Server 2017 and the right-hand side is SQL Server 2014:

As we can see, there are some additional options in SQL Server 2017's database backup task as compared to the SQL Server 2014 version. This provides more flexibility considering that the unavailability of some backup options in older versions of the Maintenance Plan Wizard makes the tool quite limited.

In older versions, to use some of these options while backing up, it was necessary to use T-SQL code like below. The code below allows to add a checksum to the backup.

BACKUP DATABASE TestDB TO DISK = 'D:\TestDB_Backups\Full\TestDB_Full.bak' WITH CHECKSUM

In contrast, in SQL Server 2017, it can be done by checking the "Perform checksum" checkbox in Maintenance Plan Wizard backup database task.

Having said that, there are still some limitations while backing up by using a Maintenance Plan task. For instance, even in SQL Server 2017, Maintenance Plans do not support mirrored backups. If we want to create mirrored backups, we would need to use T-SQL code as follows with the "MIRROR TO" option:

BACKUP DATABASE TestDB
TO DISK='D:\TestDB_Backups\Full\TestDB_Full.bak'
MIRRORTODISK='D:\TestDB_Full.bak'
WITH
   FORMAT,
   MEDIANAME='TestDBBackups';
GO

On the next window, we can choose the backup report options and move forward:

Finally, we will reach the last window and by clicking "Finish", we will have the task created:

The message below confirms that the task is successfully created:

Create SQL Server Maintenance Plan for Differential Database Backups

After having the full backup task configured, let's move to configure the differential backup task.

The process is almost the same except for we choose "Back Up Database (Differential)" and then choose the daily schedule – every day at 1:30AM:

As for the location, we choose "D:\TestDB_Backups\Differential".

Create SQL Server Maintenance Plan for Database Log Backups

The final thing to do is configure the Transaction Log backups. This is done the same as the Full or Differential and then we need to setup the transaction log backup schedule for every ten minutes:

SQL Server Backup Maintenance Plans

Finally, after setting up the three backup types, we will have all three backup plans configured:

Modifying an Existing SQL Server Maintenance Plan

In the future, these plans tasks can be edited by using the Maintenance Plan Designer. To do so, we can perform right-click on the task and choose "Modify" or just perform a double click on the task:

sql server maintenance plan modifications

Running a SQL Server Maintenance Plan

In order to execute the task without waiting for its scheduled execution, we can right-click on it and choose "Execute":

Additionally, for each task, the corresponding job is created under SQL Server Agent > Jobs:

Therefore, it is possible to execute the task by right clicking on the job and starting the job:

All in all, using the Maintenance Plan Wizard, we have configured a complete database backup solution for TestDB database by scheduling three backup tasks (Full, Differential, Transaction Log) which run due to a special order (defined by their schedule).

Conclusion

To sum up, Maintenance Plans allow us to easily implement database backup solutions. In the older versions of SQL Server, there are some limitations and in case of designing a more flexible database backup strategy, it is reasonable to use T-SQL code instead of Maintenance Plans. In contrast, in the newer versions of SQL Server, many of these problems are solved and Maintenance Plans have become a much more flexible tool for developing complicated database backup solutions.

Next Steps

To find additional information about the discussed topic please follow the links below:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2019-12-05