Nisarg Upadhyay
Drag and drop Backup database task

Automate SQL database backups using Maintenance Plans

February 12, 2021 by

In this article, we are going to learn how we can automate the backup of the SQL database using database maintenance plans. Data is one of the most important assets of any organization, and as a database administrator, it is our prime responsibility to protect it. There are various tools available that can be used to back up the data. These tools use state of the art technology to protect the data, and some of them are very costly. Instead of using these costly tools, some organizations prefer to use SQL native backups. These backups of SQL database can be automated by SQL Server Agent Jobs or Windows’ task scheduler.

The SQL Server provides the predefined maintenance tasks that can be used to perform database maintenance. These tasks are called database maintenance plans. The maintenance plans can be used to perform maintenance on the local and the remote SQL Server instance.

In this article, I am going to show how we can automate the database backups using SQL Server database maintenance plans. This article is based on a use case, and the details are the following:

  1. The Full, Differential, and Log backup of all user and system SQL databases must be taken. Schedules are following
    1. Full Backup: Every Sunday at 1 AM
    2. Differential Backup: Every day except Sunday at 2 AM
    3. Log Backup: Every 15 minutes
  2. The backup must be encrypted, and once the backup completes, the backup’s integrity must be checked.
  3. The backup location must be the following:
    1. Full Backup: \\192.168.0.103\Backups\Full Backup
    2. Differential Backup: \\192.168.0.103\Backups\Differential Backup
    3. Log Backup: \\192.168.0.103\Backups\Log Backup
  4. The backup set must be expired in 10 days

The backup process should be created as follows

Create a Maintenance Plan for Full Backup

To create a database maintenance plan, open SQL Server Management Studio (SSMS) and connect to the SQL Server instance. Once connected to the instance, expand Management Right-click on the Maintenance Plans Select New Maintenance Plan.

New maintenance plan

In the New Maintenance Plan dialog box, specify the name of the maintenance plan.

Specify the name of Maintenance plan

In the Maintenance plan configuration pan, drag and drop Back up Database Task from the toolbox and double-click to Edit it.

Drag and drop Backup database task

The first maintenance plans generate a full backup of all the SQL databases and copy the backup to the network drive. Therefore, in the general tab, click on Databases drop-down box and select All databases and click on OK.

Select All SQL Database

The destination of the backup is \\192.168.0.103\Backups\Full Backup so specify the path in Folder textbox. The backup should be copied to its corresponding directory, so click on Create a sub-directory for each database.

Specify the backup destination

Under the options tab, you can set different options for the backup files. I have changed the following configuration option

  1. Backup of SQL Database should be compressed, so chosen Compress backup from the Set backup compression drop-down box
  2. The backup of SQL Database set should be expired in 10 days, so enabled the Backup set will expire option and specified 10 days in the text box
  3. Backup of SQL Database should be encrypted using AES128 algorithm, so enabled Backup encryption and chosen AES128 from the Algorithm drop-down box. I have created a master key and a certificate to encrypt the backup set so, I have chosen BackupCert (certificate) from the Certificate or Asymmetric key drop-down box. You can read Understanding Database Backup Encryption in SQL Server article to learn more about backup encryption
  4. The backup integrity of the backup must be checked; therefore, enabled Verify backup integrity option

Specify other backup options

Click OK to save and close the Backup Database Task. Click on the Save button in the menu bar to save the maintenance plan. Now, to schedule the full database backup, click on the calendar icon.

Configure schedule to generate backup of SQL Database

A dialog box, New Job Schedule, opens. The backup should be generated every Sunday at 1:00 AM so, I have chosen weekly and specified 01:00:00 in the textbox named Occurs once and saved the schedule.

Job Schedule

Create a Maintenance Plan for Differential backups

To create a maintenance plan for differential backup, I have followed the same process that I used to create a maintenance plan to generate Full backup. The changes that I made in the configuration are the following:

In the General tab, chosen Differential from the backup type drop-down box.

Backup type

In the Destination tab, specified \\192.168.0.103\Backups\Differential Backup as backup destination.

Backup destination

In the Options tab, I have chosen the same configuration options that I used in the Database Full Backup maintenance plan.

Other options

The differential backup must be taken at 2 AM. We are generating the full backup on Sunday, so the differential backup must not be generated on Sunday, so I have configured scheduled accordingly.

  1. The frequency is weekly and enables all weekdays, excluding Sunday
  2. I specified 02:00:00 AM as job execution time

Differential backup schedule

Create a Maintenance Plan for Transaction Log backups

To create a maintenance plan for Transaction Log Backup, I have followed the same process that I used to create a maintenance plan to generate Full and differential backup. The changes that I made in the configuration are the following:

In the General tab, chosen Transaction Log from the backup type drop-down box.

Backup Type

In the Destination tab, specify \\192.168.0.103\Backups\Log Backup as backup destination. The backup files’ extension is set to *.trn.

Backup destination

In the Options tab, I have chosen the same configuration options that I used in the Database Full Backup maintenance plan.

Backup Option

The log backup must be taken every 15 minutes, so I have configured the schedule accordingly.

  1. The frequency is daily
  2. The backup interval is 15 minutes

Backup schedule

View Maintenance Plan and Jobs

You can view the database maintenance plans under the Management folder and the corresponding SQL Jobs under SQL Server Agent.

Maintenance plans and corresponding SQL Jobs

Test the Maintenance Plans

First, let us generate a full backup of the databases. To generate the full backup using the maintenance plan, Right-click on Database Full Backup.Subplan_1 and click on Execute.

Start Job at Step

The execution of the maintenance plan starts.

Maintenance Plan execution

Once it completes successfully, open the \\192.168.0.103\Backups\Full Backup.

Backup directories

As you can see in the above image, the sub-directory to save the backup for each database has been created. Open AdventureWorks2017.

Full backup is generated

As you can see, the backup has been generated. Similarly, differential and log backup has been created.

Differential backup of AdventureWorks2017

Differential backup is generated

Log backup of AdventureWorks2017

Log backup is generated

Summary

This article explained how we can automate the user and system SQL databases’ backup process using SQL Server database maintenance plans.

Nisarg Upadhyay
Backup and restore, DBAtools, Jobs, Maintenance

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

240 Views