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

---

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
• Best Backup Strategy to Schedule Database Backup in SQL Server 2012
• Way 1: Schedule Database Backup in SQL Server 2012 using SQL Server Maintenance Plans
• Way 2: Take SQL Server Database Backup Automatically using SQL Server Agent
• Way 3: Schedule Database Backup in SQL Server 2012 using Qiling Backup

SQL Server Database Backup Types You Need to Know

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.

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

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.

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.

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

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.

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.

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.

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.

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:

To create a differential backup, you can type:

To create a transaction log backup, you can type:

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

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

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, Qiling Backup.

Way 3: Schedule Database Backup in SQL Server 2012 using Qiling Backup

Here you will use SQL Server Backup feature in Qiling 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 Qiling Backup. You can try this completely free version.

• Free Download
• Windows 11/10/8.1/8/7 & Servers, 100% Secure

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 Qiling 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, File Sync, File Sync, etc. Just download it to have a try right now!

Related Articles

• Top 6 Ways to Backup MySQL Databases Easily
  There are 6 ways provided in this post to backup MySQL database, you will know the MySQL dump database commands and other ways to create MySQL backup in Linux and Windows.
• 4 Ways to Do MySQL Databases Restore from Dump File
  How to perform MySQL restore? You will get the 4 ways to make MySQL restore databases with simple clicks. Just take it for reference.
• Easiest Way to Backup MySQL to OneDrive Automatically on Any Platform
• Easiest Way to Sync Data between 2 Servers Automatically
  Syncing data between 2 servers automatically with professional file sync software Qiling Disk Master Server is the easiest way. Try it yourself.