By Alison / Last Updated June 20, 2022

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…

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.

Backup Database 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.

Backup Type and Path

6. Click Subplan Schedule (calendar icon).

Subplan Schedule

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

New Job Schedule

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

Start Job at Step

9. The scheduled automatic backup task is completed.

Scheduled Backup Task 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…

New Job

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

Name

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.

Step Commands

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.

Schedule Time Frequency

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

Start Job at Step

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.

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.

Trigger

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

Trigger

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

Start Program

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

Browse

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

Finish

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 Centralized Backupper Database.

AOMEI Centralized Backupper Database Edition 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 or differential backups on demand.

Except for backing up on single computer, you can also manage backups on all Windows PCs, workstations, and servers within LAN, with operations on one centrally controlling computer, which can promote effectiveness significantly.

Here’s a 30-day free trial for you:

Download Free TrialWin PCs & Servers
Secure Download

Before you started, to properly use Centralized Backupper Database, you need to follow these steps first:

Turn to  Computers page, click Install Client Program on the upper bar to install Agent to client computer you'd like to backup.

Install Client Program

If you choose to install Agent manually,then you need to click Uncontrolled Computers, right-click the client computers you want to back up to Request Control.

Then there will be a prompt window on correspondent client computers, permissions are need to control them.

Request Control

✦ How to Create Scheduled Automatic Backups

1. Click SQL Server Backup on Home page, or click New Task onTasks page, select SQL Server Backup.

SQL Server Backup

2. In Step 1, click Add Computers to add the client computer you want to back up.

Add Computers

3. In Step 2, select the instances and databases you want to back up (you can select single or multiple instances and databases), then click OK.

Add databases

4. In Step 3, click the text, in the prompt window click Add Storage to add a Share or NAS path as a storage end, add fill in correspondent Username and Password.

Select Path

5. After this, you can click Schedule at the lower left corner to arrange an automatic backup task. Click the computer you want to backup, and there will be a prompt window Schedule Settings. Click Schedule Type Settings you can arrange backup frequency as Once, Daily, Weekly, and Monthly.

If Daily, you can set the specific time or intervals to auto backup every day.

If Weekly, you can select specific days and time to back up once a week.

If Monthly, you can set specific days or one weekday to back up once a month.

Schedule Settings

Besides, click Advanced you can also choose full or differential backup to be performed in this task.

Advanced Settings

6. After all these settings, click Start Backup > Create and Execute Task. Then your SQL Server scheduled backup task is accomplished.

You can also click Advanced at the right upper corner of this task and select Edit Backup to change setups as you like. Besides, when restoring, you can also choose which one of these backups you'd like to restore

Edit 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 Centralized Backupper Database Edition 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 files, system, disk and partition. It’s definitely worth a try.