How to Create Scheduled Backup in SQL Server 2016

Why Schedule Automatic Backups in SQL Server 2016

Considering the security of data, it is necessary to backup databases in SQL Server 2016 regularly. However, as grows the data itself can be very large, it is time-consuming and laborious to manually operate each time you want to back up. Therefore, scheduled automatic backup is of great importance.

I will introduce 4 ways to do this as follow.

How to Create SQL Server 2016 Scheduled Backup ( 4 Ways )

Basically, there are 4 ways to create MS SQL Server 2016 Scheduled Backups. Each of them has different suitable cases and restrictions. I will list them as follow and you can click on these anchor texts to jump to the detail parts.

Use Maintenance Plan: Capable of backing up multiple databases at once. Without T-SQL scripts. Not available on SQL Server 2016 Express.

Use SQL Server Agent: Capable of arranging job order. Requires T-SQL commands. Not available on SQL Server 2016 Express.

Use scheduled Backup Batch File: Highly customizable. T-SQL script needed.

Use Automated Backup Software: Intuitive and simple. Centralized multiple backup ways. Without T-SQL scripts.

1. Use Maintenance Plan to Create Scheduled Backups

1. Launch SSMS and connect to your instance, make sure your SQL Server Agent is started. You can right-click SQL Server Agent, select Start to enable this function.>

2. Open up Management tab, right-click Maintenance Plan and select New Maintenance Plan…

3. Click Toolbox on the upper bar, select Back Up Database Task and drag it to the right blank, then double-click this task.

4. In the prompt window, choose Backup type first (Full/Differential/Transaction Log), then select specific one or more databases you want to back up. As you can see, you can choose to back up all databases, system databases, user databases and specific databases.

After that, add a path where you want to put these backups in Folder. Then you can click OK to finish the settings.

6. Click Subplan Schedule (calendar icon).

7. In the prompt window, set the Schedule type, Frequency, and Duration of this schedule on demand. Then click OK to finish.

8. Find this task in SQL Server Agent > Jobs and right-click it, select Start Job at Step to start this task.

9. The scheduled automatic backup task is completed.

2. Use SQL Server Agent to Create Scheduled Backup Job

You can also create SQL Server 2016 scheduled backup using SQL Server Agent. Here are the steps:

1. Right-click SQL Server Agent, select Start to enable this function.

2. Open up SQL Server Agent tab, right-click Job > New Job…

3. On General page, fill the name in the blank.

4. Turn to Step page, click New… and fill in Step name in the prompt window. Select T-SQL in Type, then input following statements in Command:>

DECLARE @strPath NVARCHAR(200)
set @strPath = convert(NVARCHAR(19),getdate(),120)
set @strPath = REPLACE(@strPath, ':' , '.')
set @strPath = 'filepath' + @strPath + '.bak'
BACKUP DATABASE [databasename] TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

Note: as for the ‘filepath’ part you need to fill in the path created before to store these backups. For example: D:\Backup.

Then click OK.>

5. Turn to Schedules page, click New… and fill in Schedule name, select the Frequency and Duration in the prompt window. Click OK to save these settings.

6. Find the newly created job in SQL Server Agent > Jobs, right-click it and select Start Job at Step to start.

3. Schedule Backup Batch File of SQL Databases via Task Scheduler

If you are familiar with command line, you can also create a batch file of SQL Server 2016 scheduled automatic backup, and then use Windows Task Scheduler to automate it.

1. Create a backup batch file. Here I provide you one T-SQL script as follow, which is used to back up single SQL Server database. Input them in Text Editor, then change the extension of the text file into .bat.

ECHO OFF
:: set path to save backup files e.g. D:\backup
set BACKUPPATH=filepath
:: set name of the server and instance
set SERVERNAME=instancename
:: set database name
set DATABASENAME=databasename
:: filename format Name-Date
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b)
For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b)

set DATESTAMP=%mydate%_%mytime%
set
BACKUPFILENAME=%BACKUPPATH%\%DATABASENAME%-%DATESTAMP%.bak
SqlCmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK = N'%BACKUPFILENAME%' WITH INIT, NOUNLOAD, NAME = N'%DATABASENAME% backup', NOSKIP, STATS = 10, NOFORMAT"
ECHO.
PAUSE

Note: If you want to use script to back up multiple or all databases at once, you can view this article to learn more scripts: How to Use Batch Script to Backup SQL Server Database

2. Search for Task Scheduler directly through the windows search box, and then click Create Basic Task.

3. Name this task, then move to Trigger page and select when to start this task. For example, you can choose "Daily" to perform SQL Server auto backup every day, and "Weekly" for SQL backup every week, ect. Click Next to continue.

4. Click the settled time on the left tab, to set the specific time of backup. Then Next.

5. Select Start a program in Action page. Then click Next.

6. Select Start a program at the left tab, click Browse… to add the batch file you just created, then Next.

7. Then you will see the execution of Task Scheduler, click Finish to accomplish this task.

4. Use Automated Backup Software to Create Scheduled Backups

All 3 ways introduced above are effective. But if you are not familiar with T-SQL, or you want to create SQL Server 2016 Express scheduled backup, perhaps you can try to use an automated backup software to do this for you, such as AOMEI Cyber Backup.

AOMEI Cyber Backup is an intuitive professional backup software, which makes it easy for SQL Server to auto back up every day, week, and month with simple clicks. In addition, you can also choose to make full / incremental / differential backups on demand.

Besides, it also supports VMware and Hyper-V virtual machine backup within LAN.

✍AOMEI Cyber Backup applies to Microsoft SQL 2005-2022, including SQL Express. Please click here to get this perpetual free software for your Microsoft SQL Server Database backup:

To create SQL Server 2016 scheduled backup via AOMEI Cyber Backup, you should follow these steps:

Please note these prerequisites before creating Microsoft SQL backups:
▪ Computer with both AOMEI Cyber Backup Agent and Microsoft SQL Server installed
▪ Local disk or network share to store backup files

1. Access to "Source Device" > "Add Microsoft SQL". If the database exists and the version is supported, it will appear automatically. Otherwise, you can click "​​​​​​​Add Microsoft SQL" > "Download proxy program", and install the program on the device with SQL Server installed. Then click "​​​​​​​Already installed proxy" and select the proxies you want to add.

2. Click "..."-->"Authentication" to validate the database instance. You can choose "Windows Authentication" or "SQL Authentication". Enter the credentials and click "Verify".

3. Go to create a differential SQL server database backup as following:

▪ Click "Backup Task" > "Create New Task" to back up your SQL databases.

▪ Choose backup type as "Microsoft SQL Backup", and you could also try virtual machine Backup.

▪ Enter a name for the backup task, then go to select the databases you want to back up and specify a location as a storage end.

▪ Select backup methods as full/incremental/differential and specify the backup time as daily/weekly/monthly. For example, you can specify that a full backup is performed every Monday and differential backups are performed every other day.

4. Click Start backup.

Summary

In this article, I introduced you 4 ways to create MS SQL Server 2016 scheduled backup. But if you don't like the complicated backup process, or are not familiar with T-SQL scripts, then AOMEI Cyber Backup may be more suitable for you.

In addition to centralized backup, it is also very convenient to manage backups and restore databases on all computers within LAN. Except for SQL Server backup, it can also back up your virtual machines with ease. It’s definitely worth a try.