Microsoft SQL Server Backup Automation

The backup routine for MS SQL Server includes various repetitive tasks that require a lot of time and attention. Fortunately, there are built-in Microsoft SQL Server tools that can automate many of these steps and simplify your daily routine. This article discusses those built-in tools, while also showing how to use MSP360 software for MS SQL Server automatic backup.

Using Built-In Tools

SQL Server Backup Automation

One option is to use SQL Server Agent to create a backup script and configure a schedule for SQL Server automatic backup.

You can use SQL Server Agent for backup jobs scheduling if you are running any of the SQL Server commercial editions. A free SQL Express does not have Agent feature.

For this article, we will use T-SQL scripts as examples. You can test them using Query Analyzer in SQL Server Management Studio, or use them as a backup step in SQL Server Agent. You will also need to enable SQL Server Agent, since it is disabled by default. You can enable it by opening the navigation tree for SQL Server Management Studio, right-clicking and choosing Start.

We suggest creating virtual “backup devices” in SQL Server Agent, which allow you to distribute the data from several tasks into files. SQL uses a “devices” concept for any backup job, and you can use the files on a disk drive, tape drives, etc. as a “device.” If you are using a disk drive, you can create a separate logical device for any file, and this file can include multiple backups.

The first step in the SQL Server backup automation process is to create the logical device "full_backups," which will represent target file “C:\BackupRepository\sql_full.bak.” You can do that using these commands:

USE TestDB ;  
GO  
EXEC sp_addumpdevice 'disk', 'full_backups', 'C:\BackupRepository\sql_full.bak' ;  
GO

Replace “TestDB” in the example above with the name you want to use for your database. Create at least one backup device per every backup schedule (e.g. daily, weekly, monthly, etc.).

You can also set up backup devices from the GUI by navigating in the menu to Server Objects - Backup Devices.

Now we are ready to create a backup. Choose the desired database in SQL Server Management Studio, right-click, and select Tasks - Back Up:

Next, in the backup job settings window, click Script Action to Job in the Script drop-down menu in order to automatically generate the script for SQL Server Agent:

Type necessary job name and go to the Steps tab. You will find generated script here.

Click Edit to review the script:

Modify the script if necessary - here is a quick description of the options:

• "TestDB" - target database name.
• "diff_backups" - replace with one or more previously created devices. If one of them is full or unavailable, SQL will use the others.
• WITH - contains one or more of these options:
  • COMPRESSION | NO_COMPRESSION - specify whether SQL should use compression.
  • ENCRYPTION - you can instruct SQL to encrypt the data using ALGORITHM and SERVER CERTIFICATE.
  • DESCRIPTION - small user note for the particular backup.
  • DIFFERENTIAL - allows you to create a differential database backup that contains only the data changed since the last full backup run.

You can set additional options and error-handling rules by going to the Advanced tab:

Once you are done configuring the above settings, return to the job settings and click the Schedules tab to create a recurring task. Choose the running occurrence, select desired days, and type the desired schedule plan name.

Finally, confirm the changes you made and configure notifications on the corresponding tab, if necessary.

Your backup job is now created. You can test it by clicking Start Job at Step in the context menu of the target backup job.

SQL Server Backup Verification

Simply creating a database backup is not enough. You should also ensure that you can recover the backup data in case of a disaster. Unfortunately, system administrators often face backup corruption or another problem with backed-up data; if that happens, the backups are of no use. Many external factors can prevent you from being able to perform data recovery, so we suggest automating the process of checking that you can actually recover your data from backups.

SQL Server allows you to verify a backup by using the RESTORE VERIFYONLY option as one of the job steps in SQL Server Agent:

RESTORE VERIFYONLY FROM diff_backups
GO

You can do the same using SQL Server Management Studio: Check the Verify backup when finished flag in BackUp Database properties.

Remember that backup verification only applies to the most recent backup in the backup file or device. Therefore, if you prefer T-SQL script, you need to run a verification command as a regular step after each job run.

In order to test that it is actually possible to restore the data and that MS SQL Server works as desired after the data recovery, you can use a sandbox environment.

Automatic SQL Server Backup with MSP360

Now, let’s see how to configure automatic SQL Server backup with MSP360 Backup software.

You need MSP360 Backup for MS SQL Server or MSP360 Backup Ultimate editions to be able to back up SQL databases. At the time of this article's publication, MSP360 Backup supports Microsoft SQL Server 2000-2017.

Follow these steps to set up MSP360 Backup for automatic MS SQL Server backup:

1. Start the backup wizard in MSP360 Backup by clicking the MS SQL Server button on the Home tab.

2. Select either Local or Cloud Backup or Hybrid Backup (we will use the first option in this example) and choose target storage account or create a new one.

3. Provide the name for this backup plan and proceed. At this point, you need to connect MSP360 Backup to the target SQL Server instance. MSP360 Backup will also verify the necessary permissions for you. If you have created a separate backup account in SQL Server, use the Authentication menu to specify its credentials.

4. Select the databases you want to back up. We chose TestDB in this example.

5. Specify a Retention policy for your backup to save only the necessary number of copies. This helps to prevent you from running out of space or paying more than necessary when using cloud storage.

6. The scheduling step allows you to create a “run once” job or configure one of the available schedule templates. In most cases, we suggest using the Recurring (predefined templates) schedule, which includes a set of full, differential, and transaction log backups, which you can modify depending on your needs.
If that schedule is not what you are looking for - check Recurring (advanced schedule) and create your own template.

7. Specify recurrence details from a set of templates on the next step. In the example below you will have a backup every hour, with daily differentials and weekly fulls.

8. Set any other advanced options you wish to configure, such as pre/post actions and notifications settings, and complete the backup plan creation.

Conclusion

MS SQL Server allows you to automate most data protection tasks, such as backup, verification, and retention. It is possible to write scripts to perform any action you want to include in the backup plan using T-SQL scripts.

However, if you don’t want to have to write scripts manually or learn T-SQL, check MSP360 Backup for MS SQL Server. MSP360 Backup provides automatic SQL Server backup to local or cloud storage of your choice, system image backup, bare-metal recovery and more, without requiring scripting or extensive customization.