Types of Backup in SQL Server

In this SQL Server tutorial, we will learn about Backup in SQL Server, Different types of Backup in SQL Server, and will cover the following topics.

• What is a backup in SQL Server
• Full Backup in SQL Server
• Differential Backup in SQL Server
• Transaction Log Backup in SQL Server
• Tail-Log Backup in SQL Server
• File Backup in SQL Server
• File Group Backup in SQL Server
• Partial Backup in SQL Server
• Copy-Only Backup in SQL Server
• Mirror Backup in SQL Server
• Split Backup to multiple files in SQL Server

Backup in SQL Server

A backup, also known as a data backup, is a copy of computer data that is 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. A 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.

The backups in SQL Server is a huge topic itself as there are many methods and options available related to backups. So in this post, we will discuss different types of backup in SQL Server and try to understand which backup-and-restore strategy is beneficial for us.

Read: How to Create a Database in SQL Server 2019

Full Backup in SQL Server

• A full backup is a foundation for every other type of backup available in SQL Server.
• And as the name implies, a full backup backs up everything. It creates a complete duplicate of the database, containing all of the database’s objects, such as tables, procedures, functions, views, and indexes.
• A full backup helps to quickly restore a database to the same condition as it was at the time of the backup.
• It is important to run the full backup at least once before implementing any other type of backup in SQL Server.

In SQL Server, there are mainly two ways through which we can implement a full backup. And we will discuss both with the help of an example.

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

Using Transact-SQL

Now 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 which is “BAK” in the above syntax. Now, for example, consider the following query given below, used to fully backup a database named “sqlserverguides“.

BACKUP DATABASE sqlserverguides
To DISK='E:\Backup\sqlserverguides_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, next select the Tasks option, and then click on “Back Up…“. After this, a backup database window will appear.

• Next in the General category page, first, 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.

• After adding the destination path for the backup file, click on the “OK” option in the Backup Database window.

After successful execution of the above steps, a new backup file will appear in the destination folder.

Read: Saving changes is not permitted in SQL Server

Differential Backup in SQL Server

Now there can be situations when we have a very huge database containing lots of data. In such cases taking full backup for each change could be more time-consuming, and also it will waste a lot of storage. To handle these cases efficiently, we can use the differential database backup in SQL Server.

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 that are made since the last full backup. Thus, the differential backup is much faster in comparison with full backup, and it also saves a lot of storage.

However, when data changes over time, the size of the differential backup expands as well. And for data restoration, first, we need to restore the recent full database backup and then the latest differential database backup.

Using Transact-SQL

Now 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

Now, for example, consider the following query given below to implement differential backup a database named “sqlserverguides“.

BACKUP DATABASE sqlserverguides
To DISK='E:\Backup\sqlserverguides_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.

Using SQL Server Management Studio

• First, connect to the Database engine using your credentials.
• Now go to Object Explorer, right-click the database that you want to backup, next select the “Tasks” option, and then click on “Back Up…“. After this, a backup database window will appear.

• Next on the General page, first, select the database for backup from the drop-down list and then select the Backup type as Differential.

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

• In the end, click on the “OK” option to create a differential backup file for your database.

Read: Identity Column in SQL Server

Transaction Log Backup in SQL Server

To implement a transaction log backup, a database must be in a Full or Bulk-logged 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 sequence of logs that provide 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. So we can restore our database to a specific point in time, by first using full backup, then using the latest differential, and in the last using all the transaction log nearest to that point in time.

There are two ways to implement transaction log backup in SQL server, first by sing standard Transact-SQL, and second by using SQL server management studio.

Using Transact-SQL

• Now to implement a transaction log backup of a database in SQL Server, first, we need to ensure 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'

As mentioned in the above syntax, for a transaction log backup file, we have to use the “TRN” extension.

Now, for demonstration, consider the following query given below to implement transaction log backup of a database named “sqlserverguides“.

BACKUP LOG sqlserverguides
To DISK='E:\Backup\sqlserverguides_tlb.TRN'

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

Using SQL Server Management Studio

• First, connect to the Database engine using your credentials, then go to Object Explorer, right-click the database that you want to backup. Next, select the Tasks option, and then click on “Back Up…”. It will open a new backup database window.

• Now in the General category page, first, select the database for backup from the drop-down list and then select the Backup type as “Transaction Log”.

• Now in the Destination option, first 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.

• In the end, click on the “OK” option to create a transaction log backup file for your database.

Read: SQL Server Create Temp Table

Tail-Log Backup in SQL Server

The log files related to the database play an important role in its recovery and proper functioning. So, SQL Server provides an option of tail log backup that stores any log records that have not been backed up yet. It helps to 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;

As mentioned in the above syntax, for a tail-log backup file we have to use the “.log” extension.

Now, for demonstration, consider the following query given below, used to implement tail-log backup of a database named “sqlserverguides“.

BACKUP LOG sqlserverguides
To DISK='E:\Backup\sqlserverguides_tail.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.

Read: Delete Duplicate Rows in SQL Server

File Backup in SQL Server

• In SQL Server, every database has at least two operating system files associated with it.
• First is the data file, which contains data and database objects such as tables, indexes, stored procedures, and views.
• And the seconds is the log file, which contains some information that is used to recover all transactions in the database.
• For allocation and administration purposes, data files can be grouped in filegroups.

In the above topics, we have discussed the full backup option in SQL Server, which backs up the entire database. But what if we want to backup an individual or multiple database files. For these implementations, we can use file backup in SQL Server.

So, the file backup allows the saving of individual data files instead of saving the entire database. But it is only useful when we have a large database with multiple data files in it.

Now we can either use Transact- SQL (T-SQL) query or SQL Server Management Studio to implement file backup. And we will discuss both methods with the help of an example.

Using Transact-SQL

Now to implement a file backup of a single file in SQL Server using a query, we can follow the following syntax.

BACKUP DATABASE database_name FILE = 'file_name' 
TO DISK = 'E:\BackupDrive\backup_file.FIL'

It is important to specify the file extension as “FIL” for the backup file. And for better understanding, consider the following example given below.

BACKUP DATABASE sqlserverguides FILE = 'sqlserverguides' 
TO DISK = 'E:\Backup\sqlserverguides_1.FIL'

In the example, we are implementing the file backup for the database named “sqlserverguides“. And we are backing up the primary file of the database, which has the same name as the database.

Using SQL Server Management Studio

• After connecting to the Database engine, go to Object Explorer, right-click the database whose files want to backup. Next, select the Tasks option, and then click on “Back Up…“. It will open a new backup database window.

• Now in the General category page, first, select the database for backup from the drop-down list and then select the Backup type either as “Differential” or as “Full“.
• Next, select “Files and filegroups” under the Backup component option.

• It will open a new window where we can select the file that we want to save.

• Now in the Destination option, first 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.
• In the end, click on the “OK” option to execute the backup task without running a query.

Read: SQL Server Row_Number

File Group Backup in SQL Server

• Each database in SQL Server has a PRIMARY filegroup that is linked to the one data file that is created by default. But we can also create more filegroups and add data files to them.
• And similar to file backup, we can also implement filegroup backup in SQL Server which allows to backup all the files that belong to a particular filegroup. So filegroup backup is useful when we want to backup single or multiple filegroups.
• Now we can either use SQL Server Management Studio or Transact-SQL query to implement filegroup backup. For SQL Server Management Studio all the steps are the same as mentioned for the file backup topic.

Now to implement filegroup backup using Transact-SQL query, we can follow the following syntax given below.

BACKUP DATABASE database_name FILEGROUP = 'filegroup_name' 
TO DISK = 'E:\BackupDrive\backup_file.FLG'

By using the above syntax, first, we have to specify the database name, and then we have to specify the filegroup name that we want to backup. And, also note the file extension for the backup file is “FLG“.

Now for better understanding, consider the following example given below.

BACKUP DATABASE sqlserverguides FILEGROUP = 'Primary' 
TO DISK = 'E:\Backup\sqlserverguides_primary.FLG'

After successfully executing the above code, we will get a filegroup backup file in the specified destination.

Partial Backup in SQL Server

The partial backup option was first introduced in SQL Server 2005, and it is used to backup all the read-write filegroups in a database. A partial backup can be performed either for Full or Differential backup, and it is supported by all the recovery modes.

The Partial backup is most useful when we have a Read-Only filegroup for our database but, we only want to backup the read-write filegroups. And to create a partial backup we can only use Transact-SQL query.

For this, we can follow the following synatax given below.

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

As shown in the syntax, to create a partial backup in SQL Server, we have to use the READ_WRITE_FILEGROUPS clause with the “BACKUP DATABASE” statement. Now for demonstration, consider the following example given below.

BACKUP DATABASE sqlserverguides READ_WRITE_FILEGROUPS 
TO DISK = 'E:\Backup\sqlserverguides_read_write.BAK'

The above example will create a partial backup of a database named “sqlserverguides” by including all the read-write filegroups in the backup. And it will exclude the read-only filegroups from the backup.

Read: IDENTITY_INSERT in SQL Server

Copy-Only Backup in SQL Server

Sometimes, taking a backup modifies the database and impacts how subsequent backups are restored. To overcome this challenge, SQL Server offers the copy-only backup option.

The Copy-Only backup is a special type of full backup, which is independent of the standard backup routine. It does not affect the overall backup and restoration process of a database, and it also supports all the recovery models of a database. Also, copy-only backup is available for “Full” and “Transaction Log” backup, it will not work in the case of Differential backup.

Note:- Please remember, the transaction log backup only works in the case of the Full or Bulk-logged recovery model. So, the copy-only option for transaction log will also work only in the case of Full and Bulk-logged.

Now to create a copy-only backup in SQL Server, we can either use Transact-SQL query or SQL Server Management Studio.

Using Transact-SQL

To implement the copy-only option with Full or Transaction Log backup, we can follow the following syntax given below.

--For full backup
BACKUP DATABASE database_name
To DISK='E:\BackupDrive\backup_file.BAK'
WITH COPY_ONLY

--For transaction log backup
BACKUP DATABASE database_name
To DISK='E:\BackupDrive\backup_file.log'
WITH COPY_ONLY

Now for better understanding, consider the following example used to back up a database named “sqlserverguides” with a copy-only option.

BACKUP DATABASE sqlserverguides
To DISK='E:\Backup\sqlserverguides_copy-only.BAK'
WITH COPY_ONLY

The above query will create a copy-only backup file for the “sqlserverguides” database in the specified destination.

Using SQL Server Management Studio

• First, connect to the Database engine and then under Object Explorer, right-click the database that you want to backup, next select the “Tasks” option, and then click on “Back Up…“. After this, a backup database window will appear.

• Next on the General page, first, select the database for backup from the drop-down list and then select the Backup type either as “Full” or as “Transaction Log“.
• After this, thick mark the “Copy-only backup” option and provide the destination path for the backup file under the “Destination” option.

• In the end, click on the “OK” option to create a copy-only backup file for your database.

Read: SQL Server Add Column + Examples

Mirror Backup in SQL Server

The Mirror backup option in SQL Server is available for SQL Server enterprise editions. And it is used to create an exact copy of a backup and store them at different locations. Using mirror backup we can create a maximum of 3 copies of the backup and store them at different destinations while taking the original backup.

Now to create a mirror backup in SQL Server, we can follow the following syntax given below.

BACKUP DATABASE database_name
To DISK='E:\BackupDrive1\backup_file.BAK'
MIRROR To DISK='D:\BackupDrive2\backup_file.BAK'

To specify the destination path for the mirror backup file, we have to use the MIRROR clause. After this, we can specify the destination path where we want to store the mirror file.

Split Backup to multiple files in SQL Server

Split backup is a method of splitting a SQL Server database backup into multiple files. When we implement a split backup on a database, the SQL Server engine creates multiple backup files, each of which is divided into the number of files specified in the backup command.

This helps to break a large database backup into multiple chunks of the backup files and hence saves time and resources.

Now to implement this we can either use Transact-SQL query or SQL Server Management Studio. And we will implement both methods with the help of examples.

Using Transact-SQL

For this implementation, consider the following syntax given below.

BACKUP DATABASE database_name
To DISK='E:\BackupDrive\backup_file_1.BAK',
DISK='E:\BackupDrive\backup_file_2.BAK'

The above syntax will create two backup files of the database, but we can also specify more files as well.

Now for better understanding, consider the following example used to back up a database named “sqlserverguides“.

BACKUP DATABASE sqlserverguides
To DISK='E:\Backup\sqlserverguides_backup_1.BAK',
DISK='E:\Backup\sqlserverguides_backup_2.BAK',
DISK='E:\Backup\sqlserverguides_backup_3.BAK'

The above-illustrated example will divide the database backup into 3 different files, and we have to restore all of them to restore the entire database.

Using SQL Server Management Studio

• Under Object Explorer, right-click the database that you want to backup. Next, select the Tasks option, and then click on “Back Up…“. It will open a new backup database window.

• Now in the General category page, select the database for backup and Backup type from the drop-down list.
• Now under the Destination option, first 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 to specify the split, we have to add multiple filenames in the destination menu.

• In the end, click on the “OK” option to divede the database backup into multiple files.

You may like the following sql server articles:

• SQL Server Agent won’t start
• SQL Server drop table if exists
• MySQL vs SQL Server – Key Differences in Details
• Exception Handling in SQL Server
• SQL Server convert integer to string + 12 Examples

So in this SQL Server tutorial, we have learned about Different types of Backup in SQL Server, Different situations to use backup in SQL Server, and we have also covered the following topics.

• What is a backup in SQL Server
• Full Backup in SQL Server
• Differential Backup in SQL Server
• Transaction Log Backup in SQL Server
• Tail-Log Backup in SQL Server
• File Backup in SQL Server
• File Group Backup in SQL Server
• Partial Backup in SQL Server
• Copy-Only Backup in SQL Server
• Mirror Backup in SQL Server
• Split Backup to multiple files in SQL Server