Schedule Database Backup in SQL Server 2012/2014/2016/2019

Need to schedule database backup in SQL server 2012/2014/2016/2019? You can refers to this article and you will learn 3 methods to make it.

Delia

By Delia / Updated on June 13, 2023

Share this: instagram reddit

SQL Server database backups is essential to keep business data security, thus you can restore it in  the event of a disaster and greatly reduce data loss risk.

Anyway, you need to create schedule database backup in SQL server 2012/2014/2016/2019. In this article, you will learn all you need to know about SQL Database backup, including backup types, best backup strategy and stepwise guide to backup your database.

SQL Server Database Backup Types You Need to Know

SQL Server

SQL server offers many backup types, in the following, you will learn 5 common used types, namely full backup(also known as database backups), differential backup, transaction log backup, file-group and file, copy-only backup, and each of them backup different items.

Full backup: It is the most common backup type that includes everything, such as, objects, system tables data, and transactions that occur during the backup.

Differential backup: It only backup data since the last full backup and does not include the redundant data. And all the previous differential backups are redundant, so you can delete it if you are sure you don’t need these backups.

Transaction log backup: It will backup all of the transactions (including both DML and DDL) that have occurred since the last log transaction log backup or truncation. With it, you can restore database to a particular point in time aka point-in-time recovery if your database is full recovery model, right before a data loss event, for example.

File-group and file backup: It will store all related data in files or file groups and requires the transaction log backup that covers all of the file groups from beginning to end. It’s best for large database backup, multiple files or filegroups, for example.

Copy-only backup: It is independent of the sequence of conventional SQL Server backups, which will not change the database and affect how later backups are restored. It has two types, including full database backups and transaction log database backups.

Best Backup Strategy to Schedule Database Backup in SQL Server 2012

Speaking of the best backup strategy to schedule database backup in SQL Server, there are multiple factors you need to consider, backup location, backup types, single or schedule backup, backup status etc. Then, let’s have a closer look at them.

🔹The backup location need to be separated from where the database reside, otherwise the failures occurs to database will also affect the backup image. And there is possibility that it will be damaged.

🔹For backup types, you need to create full database backup, but one backup is not enough, because you may encounter unexpected failures or change the database. Thus, backing it up automatically or on a schedule is necessary, such as, weekly. And optionally, you could choose to create a serious of differential backup at a shorter interval, such as, daily, which will include all the changes until the next full backup created.

🔹You need to take transaction log backups at frequency intervals, which can help you minimize work loss exposure and truncate the transaction log. For intervals, it depends on the important of your data, the size of the database, and the workload of the server.

🔹In addition, you need to verify backups, including the backup image was created successfully, the backup is intact and readable, the backup can be restored successfully, and all the transactions are consistent.

Way 1: Schedule Database Backup via SQL Server Maintenance Plans

Now, after learning above information, you may have a basic understanding of SQL server database backup. Then, we’ll get started to create scheduled automatic SQL database backup using SSMS(SQL Server Management Studio).

This is the first way, we will use Management Plans in the Microsoft SQL server management studio, this is relatively simple, because it has an point and click interface but only supports some basic backup options. You can follow the following steps.

Step 1. Click Start and find the Microsoft SQL Server 2012. Then, select SQL Server Management Studio.

Step 2. In the main page of Microsoft SQL Server Management Studio, you will be asked to connect, select server type and server name, click Connect.

Connect SQL Server 2012

Step 3. In the Object Explorer, expand Management, right-click Maintenance Plans and select New Maintenance Plan...

New Maintenance Plans

Note: You may be told that the agent XPs is not enabled. You need to enable it by typing the following command in the new query window.

sp_configure 'show advanced options', 1;  

GO  

RECONFIGURE;  

GO  

sp_configure 'Agent XPs', 1;  

GO  

RECONFIGURE  

GO

Enable Agent XPs

Step 4. Set a name for your new Maintenance Plan and click OK. Then, you will see the Maintenance Plan configuration page and Toolbox.

Step 5. In the Maintenance Plan configuration page, click the calendar icon under the Schedule option.

Schedule Option

Then, select Schedule type, Frequency, Daily frequency and write a unique description, click OK.

Schedule Database Backup

Step 6. In the Toolbox, go to Maintenance Plan Tasks and double-click the Back Up Database Task.

Step 7. Right-click the backup database task and select Edit.

Edit Back Up Database Task

Then, select Backup type, Backup component(Database, File and filegroups, Copy-only Backup ), back up to disk or tape, and tick Create a backup file for every database, Verify backup integrity, click OK.

Set Database Backup Task

Step 8. Go to Object Explorer > SQL Server Agent > Jobs and check the backup database task by right-click it and select Start job at Step... from the drop-down menu.

Tip: If you are worried scheduled backups taking up too much space, you can also use maintenance plan to auto delete old backups.

Test Maintenance Plan

As mentioned earlier, Maintenance Plans is very friendly to beginners, because of its easy to use GUI interface and value-added features (compared with manually written scripts ). But these are at the expense of an inability to customize many options or exercise much granular control.

In a simple word, this method is easy to use, but time-consuming and tedious. If you are not a beginners or don’t want to waste much time creating multiple maintenance plans, please keep reading below.

Way 2: Take SQL Server Database Backup Automatically using SQL Server Agent

This is the second method, we will use SQL Sever Agent in Microsoft SQL server management studio to perform SQL server backup database using script. It gives you more space for customization the database backup, but requires learning many related backup parameters. And you may not clearly understand these parameters, which will lead to human errors. To make it, you can follow steps below.

Step 1. Connect the Microsoft SQL Server Management Studio. Then, go to Object Explorer > SQL Server Agent, select Job and then New Job... from the context menu.

SQL Server Agent New Job

Note: If this is your first time use SQL Server Agent, you may find it shows SQL Server Agent(disabled). At this time, right-click it and select Start to enable it.

Step 2. In the New job configuration page, type a name for this task. Then, go to the command box and create a new backup step with the following command. Click OK to save changes.

Before you start, you need to learn some parameters of BACKUP DATABASE command.

  • The syntax is BACKUP DATABASE databaseName.
  • The “TO DISK” option specifies the backup should be written to disk. You need to specify the backup location and filename to create backup.

To create a full backup, you can type:

BACKUP DATABASE [SQL2012]

TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\SQL2012.bak'

WITH CHECKSUM;

SQL Server Agent Script

To create a differential backup, you can type:

BACKUP DATABASE [SQL2012]

TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\SQL2012.bak'

  WITH CHECKSUM;

  BACKUP DATABASE [SQL2012]

TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\SQL2012.bak'

   WITH DIFFERENTIAL;

   WITH CHECKSUM;

To create a transaction log backup, you can type:

BACKUP LOG [SQL2012]

TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\SQL2012.log'

Step 3. Select Schedule and New to set schedule settings for this backup task. Then, click OK.

New Job Schedule

Step 4. Select the created backup task, right-click it and choose Start job at Step... to check it.

Check Backup Task

That’s all for schedule database backup in SQL Server 2012, you can also use these methods to schedule database backup in SQL Server 2014/2016/2019.

But if you are not familiar with database backup, you may feel frustrated and helpless. And if you want to schedule database backup in SQL Server 2012 Express, you will find it does not support Maintenance Plans or SQL Server Agent, thus as a result, the process will be even more complicated.

First, you need to manually create a stored procedure that is able to dynamically generate the backup file name as well as what type of backup to run Full, Differential or Transaction Log backup. Then, create a SQLCMD files to run the backup commands. At last, you need to add SQLCMD.EXE with the help of Scheduled Task Wizard.

If you have tried this before, you might find this a headache Then, is there an easier way to achieve this goal? The answer is YES, you could try a third-party SQL database backup software, AOMEI Cyber Backup.

Way 3: Schedule Database Backup in SQL Server 2012 using AOMEI Cyber Backup

Here you will use SQL Server Backup feature in AOMEI Cyber Backup to take SQL Server database backup automatically, it applies to 2005\2008\2008 R2\2012\2014\2016\2017\2019\2022 and corresponding SQL Server Express. Thus, you can use one software to backup all of your SQL Server within simple steps.

Download and install AOMEI Cyber Backup. You can try this completely free version.

Download FreewareMicrosoft SQL Server 2005-2022
Centralized and secure SQL backup

1. Run AOMEI Cyber Backup on the server machine, click Source Device -> Microsoft SQL.

Microsoft SQL

2. 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 device with SQL installed.

Add Device

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

Choose Authentication Method

4. Click Backup Task on the left menu bar, select Backup Task. Click Create New Task to open the task creating page.

Create New Task

5. Choose backup type as Microsoft SQL Backup, and set the Task Name as you like.

Backup Type

6. Select Device Name to specify the SQL instances and databases to back up. You can select one or multiple database flexibly.

Select Database

7. Select Target to save backups. You can specify a local or network path

Choose Target

8. Last but not least, you can set up a Schedule to run the SQL database backup automatically. Meanwhile, you can select the backup method as Full, Incremental or Differential Backup

✍Schedule Backup helps you backup your database automatically. The schedule setting is enabled by default. You can edit or disable it on your needs, or disable it.
✍Backup Cleanup is an advanced feature that can automatically remove history backup versions based on a rule to save your storage space. You can upgrade AOMEI Cyber Backup to enjoy it.
✍Email Notification enables you to receive email notifications when the task is abnormal or successful. Gmail Server, Hotmail Server and Custom SMTP Server are supported by now. Please note SMTP server authentication is required and cannot be empty.

Click Start Backup to execute the task. A completed task will be listed in the Backup Task tab. You can edit it or restore databases from it as you need.

Backup Cleanup

Final Words

As you can see from above, schedule database backup in SQL server 2012/2014/2016/2019 (Express) is not an easy task for common users to understand and execute. Therefore, you could try a reliable SQL server backup software like AOMEI Cyber Backup.

It can help you backup database easily and quickly in a few steps, and you can backup once or set daily, weekly, monthly schedule backup. For advanced settings, you can choose to create full backup or differential backup to backup all the items or only changes during the schedule task.

In addition, it still provides other backup task for home users, such as, file backup, system backup, disk backup, real-time sync, mirror sync, etc. Just download it to have a try right now!

Delia
Delia · Editor
Delia owns extensive experience in writing technology-related blog posts, and has been a part of AOMEI since 2020 to provide expertise in data security and disaster recovery. She works with Windows operating systems, SQL databases, and virtualization platforms such as VMware and Hyper-V, specializing in troubleshooting and advising on data protection and migration.