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

---

To ensure business data security, SQL Server database backups are crucial. They allow for the restoration of the database in the event of a disaster, significantly reducing the risk of data loss.

To create a schedule database backup in SQL Server 2012/2014/2016/2019, you'll need to set up a backup strategy. This involves understanding the different types of backups (e.g.

• 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
• To take SQL Server database backup automatically using SQL Server Agent, you can create a maintenance plan in SQL Server Management Studio. First, connect to your SQL Server instance, then navigate to Management -> Maintenance Plans.
• To schedule a database backup in SQL Server 2012 using Qiling Backup, you can follow these steps. First, open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.

SQL Server Database Backup Types You Need to Know

SQL Server offers various backup types, including full backup, differential backup, transaction log backup, file-group and file backup, and copy-only backup. Each of these types backs up different items, with full backups backing the entire database, differential backups backing the changes made since the last full backup, transaction log backups backing the transaction log, file-group and file backups backing specific files or groups of files, and copy-only backups backing the database without affecting the backup chain.

Full backup: Full backup is the most common type of backup, which includes all data, such as objects, system tables, data, and transactions that occur during the backup process.

Differential backup: It only backs up data since the last full backup and doesn't include redundant data, making all previous differential backups redundant and deletable if not needed.

Transaction log backup: The log backup will capture all transactions, including DML and DDL operations, that have taken place since the last log backup or truncation. This allows for point-in-time recovery, enabling the database to be restored to a specific point in time, just before a data loss event, if the database is set to full recovery model.

File-group and file backup: The database will store all related data in files or file groups and requires a transaction log backup that covers all of the file groups from beginning to end, making it suitable for large database backups, especially those with multiple files or filegroups.

Copy-only backup: The SQL Server backup is independent of the sequence of conventional backups, allowing for flexible backup and recovery. It has two types: full database backups and transaction log database backups.

Best Backup Strategy to Schedule Database Backup in SQL Server 2012

When it comes to scheduling a database backup in SQL Server, there are several factors to consider, including the backup location, types of backups to perform (full, differential, or transactional), whether to perform a single backup or schedule regular backups, and the backup status. These considerations can help ensure that your database is properly backed up and that you can recover it in case of data loss or other issues.

🔹To ensure data safety, the backup location should be separate from the database location, preventing failures in the database from affecting the backup image and minimizing the risk of data damage.

🔹To ensure database backup reliability, creating a full database backup is necessary, but a single backup is not sufficient due to potential failures or changes. Therefore, it's essential to automate or schedule backups, such as weekly, to ensure continuity. Optionally, a series of differential backups can be created at shorter intervals, like daily, to capture all changes until the next full backup is created.

🔹You need to take transaction log backups at regular intervals to minimize work loss exposure and truncate the transaction log, with the frequency depending on the importance of your data, the size of the database, and the workload of the server.

🔹To ensure a successful backup, you should verify that the backup image was created successfully, that the backup is intact and readable, that the backup can be restored successfully, and that all transactions are consistent. This involves checking the backup's integrity and ensuring that it can be used to restore the system to its previous state.

Way 1: Schedule Database Backup via SQL Server Maintenance Plans

After learning about SQL server database backup, we can proceed to create a scheduled automatic SQL database backup using SSMS (SQL Server Management Studio).

To use Management Plans in Microsoft SQL Server Management Studio, follow these steps. This method is relatively simple, with a point-and-click interface, but only supports basic backup options. You can create a Management Plan by selecting the "Management Plans" option in the Object Explorer, then clicking "New" to create a new plan.

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...

If you're experiencing issues with your agent XPs, you can try enabling it by running a command in a new query window. This command should resolve the problem and allow your agent XPs to work properly. Please note that you may be prompted to enable it, in which case you can simply follow the instructions provided.

Step 4. To create a new Maintenance Plan, set a name for it and click OK, which will then open the Maintenance Plan configuration page and the Toolbox.

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

Select the schedule type, frequency, and daily frequency, then write a unique description and 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.

Select the backup type, component, destination, and options, such as verifying backup integrity, and create a backup file for each database, then click OK.

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

Tip: If you're concerned that scheduled backups are consuming too much space, you can utilize a maintenance plan to automatically delete old backups.

Maintenance Plans offers a user-friendly interface and valuable features, but at the cost of limited customization options and control, making it more suitable for beginners.

This method is straightforward to use, but it can be time-consuming and tedious, especially for those who are not beginners or prefer not to spend a lot of time creating multiple maintenance plans.

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

To perform a SQL Server backup database using a script, you can utilize SQL Server Agent in Microsoft SQL Server Management Studio. This method provides more flexibility for customizing the database backup, but requires knowledge of various backup parameters, which can be overwhelming and lead to human errors if not understood clearly. To accomplish this, follow the steps below.

Step 1. To create a new job in Microsoft SQL Server Management Studio, connect to the server and navigate to Object Explorer > SQL Server Agent. From there, select Job and choose New Job... from the context menu. This will open the New Job dialog box, where you can define the job's properties, schedule, and steps.

If this is your first time using SQL Server Agent, you may find it shows SQL Server Agent(disabled). To enable it, right-click it and select Start.

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 command "Backup" and 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 indicates that the backup should be written to a physical storage device, such as a hard drive, and requires specifying the backup location and filename to create the 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.

In SQL Server 2012, you can schedule a database backup using various methods. To automate the backup process, you can use SQL Server Agent jobs, which can be created through the SQL Server Management Studio (SSMS) or SQL Server Management Studio (SSMS) Express.

If you're not familiar with database backup, you might feel frustrated and helpless. Additionally, if you're using SQL Server 2012 Express, you'll find that it lacks support for Maintenance Plans or SQL Server Agent, making the process of scheduling database backup even more complicated.
To automate SQL Server backups, create a stored procedure to dynamically generate the backup file name and specify the backup type (Full, Differential, or Transaction Log). Then, create a SQLCMD file to run the backup commands. Finally, use the Scheduled Task Wizard to add SQLCMD.EXE and schedule the backups.
If you've previously attempted this, you might find it a hassle. Fortunately, there's a simpler way to achieve your goal. You can consider using a third-party SQL database backup software, such as 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, which applies to all versions of SQL Server from 2005 to 2022, including SQL Server Express, allowing you to backup all your SQL Server databases with simple steps.

Download and install Qiling Backup, a completely free version available for you to try.

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

Final Words

Scheduling database backups in SQL Server 2012/2014/2016/2019 (Express) can be a complex task for non-technical users. However, a reliable SQL server backup software like Qiling Backup can simplify this process, making it easier to automate database backups and ensure data protection.

DB Browser for SQLite can help you easily and quickly backup your database with just a few steps. You can choose to backup once or set a schedule for daily, weekly, or monthly backups. For more advanced settings, you can select to create a full backup, which includes all database items, or a differential backup, which only includes changes made during the scheduled task.

The software offers various backup options for home users, including file backup, system backup, disk backup, and file synchronization, making it a comprehensive backup solution.

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.