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:
- Run SQL Server backup for selected databases on a schedule
- Compress & encrypt the backups
- Upload the backup to a remote destination – network, NAS, FTP on one of the cloud storages (Dropbox, AWS, OneDrive, SkyDrive, etc..)
- Send email notification on backup success or failure
The most popular SQL Server backup automation options that we review here are:
Microsoft SQL Server Management Studio & SQL Server Agent
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:
- The first download, install it, run and connect to your SQL Server. Then select the databases to backup.
- 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”.
- 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.
- Compression is set by default and you can set encryption if you want to
- Press “Run Now” to test the whole job. Then just the program – the job will run on schedule.
- 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:
- Open your SQL Server Management Studio
- In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance
- Expand Databases and then right-click one of the databases you want to backup
- Tasks -> Create backup
- In the Database drop-down list, verify the database name.
- The Recovery model text box is for reference only.
- In the Backup type drop-down list, select Full (after creating a full database backup, you can create a differential database backup)
- In the Destination section, use the Backup to drop-down list to select the backup destination.
Backup Options Page
- To view or select the backup options, click Backup Options in the Select a page pane.
- In the Name text box either accept the default backup set name or enter a different name for the backup set.
- Specify when the backup set will expire and can be overwritten without explicitly skipping verification of the expiration date.
- In the Compression section, use the Set backup compression drop-down list to select the desired compression level.
- In the Encryption section, use the Encrypt backup checkbox to decide whether to use encryption for the backup.
- 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:
- In the Object Explorer panel, under the SQL Server Agent node, right-click Jobs and select New job from the context menu
- In the New, Job dialog enter a job’s name
- 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.
- Click ok to add a step, and click OK to create a job
- To schedule a job, in the New Job dialog, under the Schedule tab click New.
- In the Job Schedule select an occurring frequency, duration, and a start date and click OK:
- 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):
- 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)
- 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'"
- 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:
- 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.
- Double-click Add Scheduled Task.
- In the Scheduled Task Wizard, click Next.
- Click Browse, click the batch file that you created in step B, and then click Open.
- Type SQLBACKUP for the name of the task, click Daily, and then click Next.
- 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.
- In the Enter the user name field, type a user name and then type a password in the Enter the password field.
- Click Next, and then click Finish.
- Execute the scheduled task at least one time to make sure that the backup is created successfully.
- Use SQL Server Management Studio or Sqlcmd to create the following stored procedure in your master database:
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.
- Download MaintenanceSolution.sql.
- Open that script in SQL Server Management Studio
- In the script, find this line:
SET @BackupDirectory = N'C:\Backup'
and replace C:\Backup with the path to your backup directory.
- 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.
- Execute MaintenanceSolution.sql.
You could find more details on Ola’s site.
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.
20 thoughts on “How to automate SQL Server database backups”
This MS SQL MS process does not work on “remotely hosted” databases (most websites that aren’t corporate) because there doesn’t seem to be any backup location option that isn’t “disk” by which it clearly means the disk the database is already sitting on and will not let you specify something local. This seems a bizarre limitation. Do you know of any way to force it to backup to a LOCAL drive (meaning local to your MS SQL MS installation – your own computer – not local to where the database is sitting on some shared server-host)?
Please find more details on how to backup remote SQL Server databases at https://sqlbackupandftp.com/blog/remote-sql-server-backups-explained
This really answered my problem, thank you!
Thanks for the wonderful post.
After creating the batch file and testing I get this error:
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user ‘xxx\yyy’
I’m searching on how to supply credentials for sqlcmd, It would be nice if you update this great article with such info.
It is any option or script to turn of pc after backup?
Yes, you can use the following script shutdown.exe /s /t 00
Hi, how to restore the DB from a file created using the Transact-SQL option above?
I’m stuck with a BAK file created (by someone else) using the exact script presented here, but I get “The media family…is incorrectly formed” when using sqlcmd or SSMS.
Such an error can occur when restoring to an older version of the SQL Server rather than the version of the SQL Server where a backup was created. To find out what SQL Server version you are using, please run the following T-SQ: Command: SELECT @@ Version
To resolve the issue, please try to update SQL Server to the latest version. If there is no way to update the server, then you can raise a separate temporary server, with the latest version of the SQL Server (you can use the development edition) on another machine, restore a backup file on this new server, and then generate a script for the restored database:
1. Via SSMS, click on the Task > Generate Script, and in ‘advanced options’ specify “Types of data to script: schema and data. Script for Server Version: your SQL Server version.”
2. Via SQLBackupAndFTP, go to the connection settings, set the Microsoft SQL Server (remote) as your connection type, and select the correct version of your SQL Server in the advanced settings of your backup job settings. Just go to the “Backup options” section > click on the “More settings” link > Choose “Target engine type” to “Standalone” and choose your version of SQL Server at the “Target SQL Server version” section.
When I try to execute sqlcmd -S. \ SQLEXPRESS -E -Q “EXEC sp_BackupDatabases @ backupLocation = ‘D: \ SQLBackups \’, @ databaseName = ‘USERDB’, @ backupType = ‘F'” I get the following error message: ‘sqlcmd’ is not recognized as an internal or external command
sqlcmd is included in SQL Server SDK and is usually installed with SQL Server or SQL Server Management Studio. You can download sqlcmd by clicking on this link https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15
Hm, SQL Server isn’t installed on my machine, but there is SQL Server Management Studio and the issue still persists.
Perhaps sqlcmd is missing from the% PATH% system variable. Please check the sqlcmd utility in the following directory C:\ Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\
If there is no 170 directory, then there will be another one with a different number. There should be a file named sqlcmd.exe
If there is no such file or directory, then please install the utility from the link above.
After that add the directory where the executable file is located to% PATH%. The environment variables can be edited here: Control Panel > System > Advanced > Environment Variables
Can I make a database backup to another server that is located in the same local network?
Yes, to do it please use a shared folder. However, make sure that the user from under whom the command to create the backups runs has enough permissions to create backups in the network directory. If you create backups via SQLBackupAndFTP, then just select the folder destination and specify the credentials to access your network folder.
During the backup, I get this error message:
The server principal “Pavel” is not able to access the database “AdventureWorks” under the current security context.
This error can occur if the user is not mapped to the database. This article explains how to map a user to a database https://sqlbackupandftp.com/blog/hot-around-server-principal-nt-authoritysystem
When trying to backup, I get this error message: “Cannot open backup device ‘C:\Backups\Operating system error 3 (The system cannot find the path specified.).” I’ve checked and can confirm that such a directory exists, so why can’t I make a backup to it?
Such an error can occur if the SQL Server does not have permissions to the directory in which you are sending the backups. Please make sure that the user has the relevant permissions to this directory. More information here: https://sqlbackupandftp.com/blog/how-to-solve-operating-system-error-3