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.
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 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.
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.
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.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
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.
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.
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;
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.
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, 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.
1. Run AOMEI Cyber Backup on the server machine, click Source Device -> 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.
3. Click
4. Click Backup Task on the left menu bar, select Backup Task. Click Create New Task to open the task creating page.
5. Choose backup type as Microsoft SQL Backup, and set the Task Name as you like.
6. Select Device Name to specify the SQL instances and databases to back up. You can select one or multiple database flexibly.
7. Select Target to save backups. You can specify a local or network path
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.
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!