Types of Backup in SQL Server

As a database developer, I should protect my data in the organization. The importance of data backup cannot be ignored. Recently, I tried to back up the data on the SQL server. There are different types of backup options available in the SQL Server. Initially, I faced some challenges in choosing, but finally, I did it smoothly.

I am sharing the types of backup you can perform in SQL Server. Let’s get started.

Backup in SQL Server

A backup, also called a data backup, is a copy of computer data captured and stored somewhere so that it can be used to restore the original in the event of a data loss.

A backup copy of SQL Server data can be used to restore and recover data in the event of a failure. An SQL Server data backup is made at the database level or the level of one or more database files or filegroups. Backups at the table level are not possible in SQL Server. In addition to data backups, the full recovery model necessitates transaction log backups.

Reason for Backup

Below is the reason for the backup to restore the original state before facing the issue.

• Hardware failure
• Database corruption
• User-errors.

We can also use a backup to copy a database from one server to another. For example, we can make a backup of a database on the production server and restore it on the test server.

Types of Backup in SQL Server

Below are the most common types of backup available in the SQL server.

• Full backup
• Differential backup
• Transaction log backup
• Tail log backup

Full Backup

• As the name implies, a full backup means backing up everything.
• It is a copy of an entire database, including data pages and log files. A full backup will not short the transaction log.
• We cannot restore a differential or transaction log backup without a full backup.
• We should perform the full backup when the workload is low.

Ways of Full Backup

There are two ways to implement full backup, namely,

1. Using Transact-SQL
2. Using SQL Server Management Studio

Using Transact-SQL

To implement a full backup of a database in SQL Server, we can follow the following syntax given below.

BACKUP DATABASE database_name
To DISK='E:\BackupDrive\backup_file.BAK'

It is important to mention the file extension, BAK, in the above syntax. Consider the following query, given below, used to fully back up a database named sqlserverguides.

BACKUP DATABASE imsdb
To DISK='E:\Backup\imsdb_backup.BAK'

After successfully executing the above query, we will get a new backup file with a “.BAK” extension that we can use to restore or migrate the same database.

Using SQL Server Management Studio

• First, start SQL Server Management Studio and connect to the database engine using your credentials.
• Now, in the Object Explorer, right-click the database that you want to backup, select the Tasks option, and click on Back Up… A backup database window will then appear.

• Click the General category, select the database for backup from the drop-down list, and then select the Backup type as Full.

• Next, in the Destination option, select Back up to: as Disk and then click on the Add.. option to add the path where you want to save the backup file.

Now you can see the message The backup of the database completed successfully. Click OK.

Differential Backup in SQL Server

There can be situations when we have a huge database containing a lot of data. In such cases, taking a full backup for each change could be more time-consuming and waste a lot of storage. We can use differential database backup in SQL Server to handle these cases efficiently.

A differential backup in SQL Server saves all the changes made in the database since the last full backup. The differential database backup does not store the whole database, instead, it stores the changes made since the last full backup. Thus, the differential backup is much faster than a full backup and saves much storage space.

Using Transact SQL

To implement a differential backup of a database in SQL Server, we can follow the following syntax given below.

BACKUP DATABASE database name
To DISK='E:\BackupDrive\backup_file.BAK'
WITH DIFFERENTIAL

BACKUP DATABASE imsdb
To DISK='E:\Backup\imsdb_differential.BAK'
WITH DIFFERENTIAL

After successfully executing the above query, we will get a new backup file with the specified name. But always remember to have a full database backup before creating a differential backup of a database.

Note: For SQL Server management studio, select Differential log instead of full log, as discussed in the last topic.

Transaction Log Backup in SQL Server

To implement transaction log backup in SQL, data must be full or recovery model. The transaction log backup in SQL Server is used to store the transaction logs of a database. In a database, a transaction log file records a log sequence that provides the history of every data modification.

Because transaction log backup is incremental in nature, it allows restoring a database to a particular point in time. We can restore our database to a specific point in time by first using full backup, then the latest differential, and last using all the transaction logs nearest to that point.

Using Transact SQL

• To implement a transaction log backup of a database in SQL Server, first, we need to check that the database is set to a Full or Bulk-logged recovery model.
• Also, remember to perform a full database backup at least once before implementing transaction log backup. After this, we can use the following syntax.

BACKUP LOG database_name
To DISK='E:\BackupDrive\backup_file.TRN'

After successfully executing the above query, a new log backup file with the specified name will be created in the destination folder.

Using SQL Server Management Studio

• Open SQL Server Management Studio and connect to the database engine using your credentials.

• Now, in the Object Explorer, right-click the database that you want to backup, select the Tasks option, and click on Back Up… A backup database window will then appear.

• Click the General category, select the database for backup from the drop-down list, and then select the Backup type as Transaction log.

• Next, in the Destination option, select Back up to: as Disk and then click on the Add.. option to add the path where you want to save the backup file.

Now you can see the message The backup of the database completed successfully. Click OK.

Tail log Backup in SQL Server

SQL provides the option of a Tail log that stores any record that has not been backed up yet. This helps minimize data loss and maintain the log chain unbroken.

The tail-log in SQL Server is a special type of transaction log backup so to implement this, the database should be using the full or bulk-logged recovery models.

Now, to implement the tail-log backup on a database in SQL Server, we can use the following syntax.

BACKUP LOG database_name
To DISK='E:\BackupDrive\backup_file.log'
WITH CONTINUE_AFTER_ERROR;

Now, after successfully executing the above example, we will get a log backup file with the specified name in the destination folder.

SQL Server Recovery Models

There are three types of SQL Server database recovery models. The SQL Server recovery model determines how long to keep the datalog in the recovery model, the type of backup we perform, and the type of restore we can perform.

• Simple Recovery
• Full recovery
• Bulk-logged recovery

Simple Recovery

SQL Server automatically reduces the log-on checkpoint operations for databases that use the Simple Recovery model. This makes used space in the transaction log available for more transactions. The backup of transaction logs is not supported when using Simple Recovery.

This model is the simplest for managing transaction log backups, but it does not allow for point-in-time database restores. If you are running infrequent full and differential backups and your data is changing often, this could lead to unacceptable data loss in the event that a database needs to be restored.

Full Recovery

Until you perform a transaction log backup, all transactions under a Full Recovery model stay in the transaction log file. As would happen under the Simple Recovery model, the transaction log will never be automatically terminated.

Remember that even when changes are made to the database, information is still being stored in the transaction log if your database is set up with the Full Recovery model.

We should regularly perform transaction log backups to keep your logs from expanding to enormous sizes and possibly filling up your disk drive. The data that was backed up from the transaction log is removed after the backup is finished, freeing up space for new transactions.

Bulk Logged Recovery

With the exception of some bulk operations that are not fully logged in the transaction log (referred to as minimal logging), the Bulk-Logged Recovery model is comparable to Full Recovery. Examples of minimally logged operations are SELECT INTO, BULK import, and TRUNCATE operations. Your transaction logs might not grow as big under the Bulk-Logged Recovery model as they would under the full Recovery mode.

Conclusion

I hope this tutorial has provided enough information about the types of backup in SQL Server and covered SQL recovery models.

Read Also:

• SQL Server Agent won’t start
• SQL Server Add Column + Examples
• How to export data from SQL Server to Excel