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:
-
The Full, Differential, and Log backup of all user and system SQL databases must be taken. Schedules are
following
- Full Backup: Every Sunday at 1 AM
- Differential Backup: Every day except Sunday at 2 AM
- Log Backup: Every 15 minutes
- The backup must be encrypted, and once the backup completes, the backup’s integrity must be checked.
-
The backup location must be the following:
- Full Backup: \\192.168.0.103\Backups\Full Backup
- Differential Backup: \\192.168.0.103\Backups\Differential Backup
- Log Backup: \\192.168.0.103\Backups\Log Backup
- 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.
In the New Maintenance Plan dialog box, specify the name of the maintenance plan.
In the Maintenance plan configuration pan, drag and drop Back up Database Task from the toolbox and double-click to Edit it.
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.
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.
Under the options tab, you can set different options for the backup files. I have changed the following configuration option
- Backup of SQL Database should be compressed, so chosen Compress backup from the Set backup compression drop-down box
- 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
- 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
- The backup integrity of the backup must be checked; therefore, enabled Verify backup integrity option
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.
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.
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.
In the Destination tab, specified \\192.168.0.103\Backups\Differential Backup as backup destination.
In the Options tab, I have chosen the same configuration options that I used in the Database Full Backup maintenance plan.
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.
- The frequency is weekly and enables all weekdays, excluding Sunday
- I specified 02:00:00 AM as job execution time
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.
In the Destination tab, specify \\192.168.0.103\Backups\Log Backup as backup destination. The backup files’ extension is set to *.trn.
In the Options tab, I have chosen the same configuration options that I used in the Database Full Backup maintenance plan.
The log backup must be taken every 15 minutes, so I have configured the schedule accordingly.
- The frequency is daily
- The backup interval is 15 minutes
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.
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.
The execution of the maintenance plan starts.
Once it completes successfully, open the \\192.168.0.103\Backups\Full Backup.
As you can see in the above image, the sub-directory to save the backup for each database has been created. Open AdventureWorks2017.
As you can see, the backup has been generated. Similarly, differential and log backup has been created.
Differential backup of AdventureWorks2017
Log backup of AdventureWorks2017
Summary
This article explained how we can automate the user and system SQL databases’ backup process using SQL Server database maintenance plans.
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
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022