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
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
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
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.
:: set path to save backup files e.g. D:\backup
:: set name of the server and instance
:: set database name
:: 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)
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"
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. Click Next.
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 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:
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.
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.
✦ How to Create Scheduled Automatic Backups
1. Click SQL Server Backup on Home page, or click New Task onTasks page, select SQL Server Backup.
2. In Step 1, click Add Computers to add the client computer you want to back up.
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.
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.
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.
Besides, click Advanced you can also choose full or differential backup to be performed in this task.
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.
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.