Backup Database Task in SQL Server Maintenance Plans

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

folder stucture

Create SQL Server Maintenance Plan for Full Database Backups

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

create maintenance plan

After launching the wizard, we click "Next":

sql server maintenance plan wizard

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

sql server maintenance plan wizard

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

sql server maintenance plan wizard

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:

sql server maintenance plan wizard

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

sql server maintenance plan wizard

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

sql server maintenance plan wizard

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

sql server maintenance plan wizard

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:

sql server maintenance plan wizard

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:

sql server maintenance plan wizard

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

sql server maintenance plan wizard

The message below confirms that the task is successfully created:

sql server maintenance plan wizard

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:

sql server maintenance plan wizard

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 maintenance plan wizard

SQL Server Backup Maintenance Plans

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

sql server maintenance plans

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

sql server maintenance plans

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

sql server maintenance plan jobs

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

sql server maintenance plan jobs

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:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sergey Gigoyan 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



Comments For This Article

















get free sql tips
agree to terms