MS SQL Server Backup Automation

SQL backup routine includes various actions you should remember about. And the best way to get it done is by using backup automation. In this article, we will cover built-in tools that help to simplify your daily routine.

We will also show how to do this using CloudBerry software for MS SQL Server backup.

If using Microsoft SQL Server built-in data protection, one of the obvious options is the use of SQL Server Agent, allowing you to automate backups copying and retention. But there are also two additional routine steps:

  • Retention policies allow you to reduce the consumption of disk space.
  • Data verification is a very important step, though it is often ignored due to its apparent complexity.

Well, if you would like to ensure that the backed up database is 100% suitable for data restoration, and it contains all the dependencies necessary for proper work, you will finish with a separate virtual lab. It allows you to actually restore the data and check whether SQL Server with the restored database works fine.

MS SQL Server Backup Automation with Built-In Tools

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

Note: we will use T-SQL scripts as examples. You can test them using Query Analyzer in SQL Management Studio, or use as a backup step in SQL Agent. Please do not forget to enable SQL Agent, since it is disabled by default. Just find it in navigation tree of SQL Management Studio, right-click, and choose Start.

First of all, we suggest creating virtual “backup devices” in SQL Agent, allowing you to easily distribute the data of several tasks to 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 using a disk drive, you can create a separate logical device for any file, and this file can include multiple backups.

Let’s create the logical device full_backups which will represent target file “C:\BackupRepository\sql_full.bak”:

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

Use your DB name instead of TestDB. You can do the same via GUI in the menu Server Objects - Backup Devices. Create at least one backup device per every backup schedule (e.g. daily, weekly, monthly, etc.).

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

In the backup job settings window you can automatically generate the script for SQL Agent - just click Script Action to Job in the Script drop-down menu:

Type necessary job name and go to the Steps tab. You will find generated script here - click Edit to review:

You can modify the script if necessary - here is a quick options description:

  • TestDB - target DB name.
  • diff_backups - replace with one or more previously created devices. If one of them will be full or unavailable - SQL will use the rest.
  • 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 DB backup that contains only the data changed since the last Full backup run.

You can add additional options and error-handling rules using the Advanced tab and creating the new steps:

Now return to the job settings and go to the Schedules tab - you can create a recurring task here. Choose running occurrence, select desired days, and type desired schedule plan name.

Confirm changes made and configure notifications on the so-called tab, if necessary. Your backup job is now created, and you can forcibly test it by clicking Start Job at Step in the context menu of the target backup job.

SQL Server Backup Verification

It is not enough to only create a DB backup, you should also assure that you can recover the data in case of a disaster. Unfortunately, system administrators often face backup corruption or another undesired behavior of backed up data. Many external factors can lead you to not having a possible data recovery, so we suggest to automate its checking from time to time.

SQL Server allows you to verify that you can actually recover data from the backup archive by using the RESTORE VERIFYONLY option as one of the SQL Agent’ job steps:

RESTORE VERIFYONLY FROM diff_backups
GO

You can do the same using SQL Management Studio - just 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. So, if you prefer T-SQL script, you need to run a verification command as a regular step after each job run.

SQL Server Backup Automation with CloudBerry

If you don’t like the quite confusing SQL Server Agent interface, you may prefer a third-party backup tool. Let’s look at backup automation flow using CloudBerry Backup as an example.

Note: you need the “SQL Server Backup” or “Ultimate” edition to be able to protect SQL databases. At the moment of this post publication, CloudBerry Backup supports Microsoft SQL Server 2000-2016.

Below you will find a quick guide on how to backup SQL Server and how to verify this backup using CloudBerry Backup:

  • Start the backup wizard by clicking the MS SQL Server button on the Home tab:

  • Select whether CloudBerry Backup should do Local or Cloud Backup, or a mix (Hybrid Backup). We will use the first option as the simplest way. Next choose target storage account or create a new one.
  • Then provide the name for this backup plan and proceed next. Now you need to connect CloudBerry Backup to the target SQL instance. CloudBerry Backup will also check necessary permissions for you. If you have created a separate backup account in SQL Server - use the Authentication menu to specify its credentials.
  • Select the databases you want to protect - we chose TestDB in this example.

Select the databases you want to protect - we chose TestDB in this example.

  • You need to specify Retention policy for your backup to save only the necessary number of copies, otherwise you can run out of space or pay more than necessary when using cloud storage.

  • 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 that already includes a set of full, differential, and logs backups, which you can slightly modify depending on your needs.

If that schedule is not what you are looking for - check Recurring (advanced schedule) and create your own template.

  • 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.

  • Set the rest of advanced options, such as pre/post actions and notifications settings, and complete configuration wizard.

You can read more about backup plan configurations and SQL restoration using CloudBerry Backup in the article How to: Restore Database Backup in SQL Server.

Conclusion

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

But if you don’t like to go deeper than necessary in backup configuring - check CloudBerry Backup for MS SQL Server, allowing you to create local or cloud backups of your choice. It also includes image-based backups and bare-metal restore features.