The question “How to automate SQL Server database backups” has several answers and here we will review all of the best options. But first, let’s define what SQL Server database backup automation stands for. SQL Server backup automation is a process that includes at least the following steps:

1. 1. Run SQL Server backup for selected databases on a schedule
   2. Compress & encrypt the backups
   3. Upload the backup to a remote destination – network, NAS, FTP on one of the cloud storages (Dropbox, AWS, OneDrive, SkyDrive, etc..)
   4. Send email notification on backup success or failure

The most popular SQL Server backup automation options that we review here are:

SQLBackupAndFTP
Microsoft SQL Server Management Studio & SQL Server Agent
T-SQL
Ola Hallengren script
or you can just jump straight to the Conclusion

 

Notes to keep in mind before we begin

Note that if you do not have access to the file system of a remote SQL server like it often happens in hosted SQL servers, you will need to use a different SQL backup method described in our how to backup remote SQL database article. Here we will only cover the most common options for SQL Servers running on local, networks, dedicated or virtual servers.

SQL Server Express does not have a built-in way to schedule backups because the SQL Server Agent is not included. SQL Server Agent has a horrible overblown interface and we recommend using something else regardless of the SQL Server edition you are using.

Do not overlook how difficult it is to restore. We’ve included short references on how to do it for every tool below. For more details read How to restore SQL Server backup.

Note also, that you don’t have to backup the whole database, especially if it’s quite big. You can backup only those changes that had the place since the previous backup. For more information read about Full vs Differential backups.

How to schedule SQL Server database backups using SQLBackupAndFTP

SQLBackupAndFTP is the simplest tool to automate database backups. It is MS SQL Server backup software that runs scheduled backups (full, differential or transaction log) of SQL Server or SQL Server Express databases (any version), runs file/folder backup, zips and encrypts the backups, stores them on a network, FTP server or in the cloud (Amazon S3, Google Drive, Dropbox, OneDrive, Box, Amazon S3, Azure Storage); removes old backups, and sends an e-mail confirmation on the job’s success or failure.

There are a few simple steps to set it up:

1. The first download, install it, run and connect to your SQL Server. Then select the databases to backup.

2. Then select where to store the backups.

In this case, we’ve selected a network folder. Press “Test” to check the connection, then click “Save & Close”.

3. To create a backup schedule turn on “Schedule backups” and set the time to backup for simple full daily backups. Or click the gear button to open “Advanced backup schedule” for Differential and Transaction log backup and other options.

4. Compression is set by default and you can set encryption if you want to

5. Press “Run Now” to test the whole job. Then just the program – the job will run on schedule.

6. If you need to restore on the same computer – just select one of the  backups from the “History & restore” section, click on the 3-dot icon and click “Restore”:

To restore on a different computer, install SQLBackupAndFTP there, select “Restore” in the menu and use the interface to select the backup to restore from.

Or you can just download the backup through SQLBackupAndFTP, a file manager or the cloud destination client, unzip the file and use the standard RESTORE DATABASE command.

How to schedule SQL Server database backups using Microsoft SQL Server Management Studio and SQL Server Agent

These are the following steps you need to take to create a .bak file (backup) using SSMS:

1. Open your SQL Server Management Studio
2. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance
3. Expand Databases and then right-click one of the databases you want to backup
4. Tasks -> Create backup
   General Page
   1. In the Database drop-down list, verify the database name.
   2. The Recovery model text box is for reference only.
   3. In the Backup type drop-down list, select Full (after creating a full database backup, you can create a differential database backup)
   4. In the Destination section, use the Backup to drop-down list to select the backup destination.

   Backup Options Page

   1. To view or select the backup options, click Backup Options in the Select a page pane.
   2. In the Name text box either accept the default backup set name or enter a different name for the backup set.
   3. Specify when the backup set will expire and can be overwritten without explicitly skipping verification of the expiration date.
   4. In the Compression section, use the Set backup compression drop-down list to select the desired compression level.
   5. In the Encryption section, use the Encrypt backup checkbox to decide whether to use encryption for the backup.
5. Press OK and if everything is OK you will see a notification that a backup of the selected database is finished

To schedule backups using a SQL Server Agent job

To automate and schedule a backup with SQL Server Agent:

1. In the Object Explorer panel, under the SQL Server Agent node, right-click Jobs and select New job from the context menu
2. In the New, Job dialog enter a job’s name
3. Under the Steps tab click on the New button and create a backup step by inserting a T-SQL statement. In this case, the CHECKSUM clause has to be included in the T-SQL code.
4. Click ok to add a step, and click OK to create a job
5. To schedule a job, in the New Job dialog, under the Schedule tab click New.
6. In the Job Schedule select an occurring frequency, duration, and a start date and click OK:
7. To check a created job in the Object Explorer pane and under the SQL Server Agent ➜ Jobs node right-click the job create above and select the Start job at step option:

You can find even more detailed explanations on backups with SSMS in this article.

How to schedule SQL Server database backups using Transact-SQL

Transact-SQL (T-SQL) is Microsoft’s and Sybase’s proprietary extension to the SQL (Structured Query Language) used to interact with relational databases. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

Generally, the T-SQL command to generate a full backup may look like:

BACKUP DATABASE MyDatabase
TO backup_destination [ ,...n ]
[ WITH with_options [ ,...o ] ]

Where MyDatabase is a SQL Server database you wish to backup. backup_destination is a place where you want to write a backup. WITH with_options is a command you may use to apply different options to a backup such as compression or encryption or a description and so on. To find more information about backup options you may read an article.

As an example you may use the next code to create a full backup to a disc device:

USE MyDatabase;
GO
BACKUP DATABASE MyDatabase
TO DISK = 'C:\SQLServerBackups\MyDatabase.Bak'
WITH FORMAT,
MEDIANAME = 'C_SQLServerBackups',
NAME = 'Full Backup of MyDatabase;
GO

To schedule the backups using Transact-SQL and Windows Task Scheduler follow these steps (from original article):

1. 1. Use SQL Server Management Studio or Sqlcmd to create the following stored procedure in your master database:

      USE [master]
      GO
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE PROCEDURE [dbo].[sp_BackupDatabases]
             @databaseName sysname = null,
             @backupType CHAR(1),
             @backupLocation nvarchar(200)
      AS
      SET NOCOUNT ON;
          DECLARE @DBs TABLE (
            ID int IDENTITY PRIMARY KEY,
            DBNAME nvarchar(500)
          )
      
      INSERT INTO @DBs (DBNAME)
      SELECT Name FROM master.sys.databases WHERE state=0 AND name=@DatabaseName OR @DatabaseName IS NULL ORDER BY Name
      
      -- Declare variables
      DECLARE @BackupName varchar(100)
      DECLARE @BackupFile varchar(100)
      DECLARE @DBNAME varchar(300)
      DECLARE @sqlCommand NVARCHAR(1000)
      DECLARE @dateTime NVARCHAR(20)
      
      -- Database Names have to be in [dbname] format since some have - or _ in their name
      SET @DBNAME = '[DBNAME]'
      
      -- Set the current date and time n yyyyhhmmss format
      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
      
      -- Create backup filename in path\filename.extension format for full,diff and log backups
      SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
      
      -- Provide the backup a name for storing in the media
      SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
      
      -- Generate the dynamic SQL command to be executed
      SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
      
      -- Execute the generated SQL command
      EXEC(@sqlCommand)

   2. In a text editor, create a batch file that is named Sqlbackup.bat, and then copy the text from the following example into that file:

      sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName=’USERDB’, @backupType='F'"

   3. Schedule a job by using Windows Task Scheduler to execute the batch file that you created in step 2. To do this, follow these steps:
      1. On the computer that is running SQL Server Express, click Start, point to All Programs, point to Accessories, point to System Tools, and then click Scheduled Tasks.
      2. Double-click Add Scheduled Task.
      3. In the Scheduled Task Wizard, click Next.
      4. Click Browse, click the batch file that you created in step B, and then click Open.
      5. Type SQLBACKUP for the name of the task, click Daily, and then click Next.
      6. Specify information for a schedule to run the task. (We recommend that you run this task at least one time every day.) Then, click Next.
      7. In the Enter the user name field, type a user name and then type a password in the Enter the password field.
      8. Click Next, and then click Finish.
      9. Execute the scheduled task at least one time to make sure that the backup is created successfully.

How to schedule SQL Server database backups using Ola Hallengren script

If sometimes you need to create a backup without complex settings it’s enough to use one of the options described above. But if you want regularly maintain your database you need a maintenance plan. One of the most common ways that DBAs create database maintenance plans is to use the Maintenance Plan Wizard from within SSMS. While it is possible to create a decent database maintenance plan using the Maintenance Plan Wizard, the tool is not very flexible. As your database environment grows, the built-in tools in the SQL maintenance toolbox may prove insufficient.

Ola Hallengren has developed a series of stored procedures which together provide a maintenance solution with the additional flexibility and features required to manage your databases.

Getting started with the SQL Server Maintenance Solution is easy. Follow these steps.

1. Download MaintenanceSolution.sql.
2. Open that script in SQL Server Management Studio
3. In the script, find this line:

   SET @BackupDirectory = N'C:\Backup'

   and replace C:\Backup with the path to your backup directory.

4. In the script, find this line:

   SET @CleanupTime = NULL

   and replace NULL with your cleanup time. The cleanup time is the number of hours after which the backup files are deleted.

5. Execute MaintenanceSolution.sql.

You could find more details on Ola’s site.

Conclusion

There are a number of ways to automate SQL Server backups.

SQLBackupAndFTP is by far the simplest and includes built-in encryption and a variety of destinations so that you can more easily integrate it with the services you currently use. Additionally, its’ basic plan is free.

The traditional SSMS method is cumbersome, does not work in SQL Server Express, and is limited in destinations. T-SQL method is just bare bones, it is lacking compression, encryption, notifications, and destinations. Ola’s script is just a more advanced version of the T-SQL method.

SQLBackupAndFTP is simply the best solution to create SQL Server backups on a regular basis with flexibility and integration with the services you already use. It performs complex tasks, but its’ UI and usability is extremely simple to use. You’ll be scheduling SQL backups in less than 5 minutes.