By Delia / Last Updated June 16, 2022

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.

In addition, you still 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.

Except for these factors, you still 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.

Method 1: Schedule Database Backup in SQL Server 2012 using 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.

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

Method 3: Schedule Database Backup in SQL Server 2012 using AOMEI Centralized Backupper Database

Here you will use SQL Server Backup feature in AOMEI Centralized Backupper Database to take SQL Server database backup automatically, it applies to SQL Server 2012/2014/2016/2019 and corresponding SQL Server Express. Thus, you can use one software to backup all of your SQL Server within simple steps.

◉ Download and install Centralized Backupper Database. Then, launch it and registry for use.

◉ Select Computers > Uncontrolled Computers, select the computer with SQL server database and then click Request Control (By entering accounts or By sending a message).

Request Control

◉ Go to Controlled Computers and check if the computer with SQL server database are here.

Controlled Computer with SQL Server Database

Then you can try following steps to create a scheduled database backup task.

1. Select Tasks > New Task and select SQL Server Backup. Then, you will see the main page of Database Backup, it has 3 steps in total.

SQL Server Backup

2. In Step 1, click Add Computers and it will start to scan the controlled computer with SQL server database and list all of them.  

Add Computers

Then, select the one you want to backup and click OK.

Select Computer with SQL Server Database

3. In Step 2, you will be asked to select source, double-click the box, and click Add to scan for all the instances on the server.  

Add All SQL Server Instances

And select one or more databases you want to backup in the popping out window.

select database

4. In Step 3, you will be asked to select backup destination, double-click the box, click Add storage and type network path, display name, username and password.

Add Storage

After that, the newly added storage will be listed under the Share or NAS device tab, select it and click OK.

Share NAS Storage

5. Now things are more than half done. You just need to add backup options by clicking Settings and Schedule

Settings: It allows you to enable email notification to get backup result in time.

Schedule: It allows you to take SQL Server database backup automatically on a schedule, such as, daily, weekly, monthly. Also, you can set advanced settings, including full backup and differential backup.

Schedule Database Backup SQL Server 2012

Then, click Start Backup(Only create the task, Create and execute tasks).

Start Backup Database SQL Server 2012

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

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!