We run SQL Server backups to safeguard our databases from user errors, media failure, hardware failure, etc. There are multiple types of SQL server backups.

As part of our Server Management Services, we assist our customers with several SQL Server queries.

Today, let us look into the details of SQL Server backups and how to perform them.

Types of SQL Server backups

SQL Server Backups are the dump of its databases, to recover them in case of any corruption or emergency. Backups are essential for data protection.

Among the multiple types of backup is:

• Full Backup
• Differential Backup
• Transaction Log Backup
• Copy-Only Backup
• File Backup
• Partial Backup

Let us now look into the details of different types of SQL Server backups and how our Support Tech suggest to perform them.

• Full Backup

Full database backup captures a dump of the whole database including transaction logs. A full backup supports all three recovery models.

Below is a T-SQL script that we use to run a full backup of the SQL Server database.

— Back up the abcd database to new media set.
BACKUP DATABASE abcd
TO DISK = ‘D:\Backups\abcd_25Oct2020.bak’
WITH FORMAT;
GO

We can also do this using SQL Server Management Studio:

1. Initially, right-click on the database name
2. Select Tasks > Backup
3. Then select “Full” as the backup type
4. Select “Disk” as the destination
5. Click on “Add…” to add a backup file and type “D:\Backups\abcd_25Oct2020.bak” and click “OK”
6. Finally, click “OK” again to create the backup.

• Differential Backup

A differential backup depends on the most recent full database backup. It captures only the data that has changed since that full backup.

If our database does not have any full backup yet, we will not be able to run its differential backup. We need a base full backup to run the differential backup.

Differential backup is faster because it does not capture the full database dump but only the extent of the update since the last full backup.

Database engine reads details about updated extents from Differential Changed Map page type and captures those page ids only into the backup. We can use the differential backup in all recovery models.

Below is a T-SQL script that we use to run a differential backup of the SQL Server database:

— Create a full database backup first by running above commands.
— Create a differential database backup
BACKUP DATABASE abcd
TO DISK= ‘D:\Backups\abcd_25Oct2020.drn’
WITH DIFFERENTIAL;
GO

Similarly, we can perform this using SQL Server Management Studio.

1. Initially right-click on the database name
2. Select Tasks > Backup
3. Then select “Differential” as the backup type
4. Select “Disk” as the destination
5. Click on “Add…” to add a backup file and type “D:\Backups\abcd_25Oct2020.drn” and click “OK”
6. At last, click “OK” again to create the backup.

• Transaction Log Backup

Transaction log backup works only in the full and bulk-logged recovery models.

We cannot run a log backup in a simple recovery model. We must have at least a full database backup to run transaction log backup. If we do not perform a full backup since the database creation, we cannot execute log backup.

We should frequently run log backup to minimize work loss exposure and to truncate the transaction log that makes free space in a database log file.

To run log backup we run the below T-SQL code:

— Create a full database backup first.
— Create a log database backup by running below command.
BACKUP LOG abcd
TO DISK = ‘D:\Backups\abcd_25Oct2020.trn’
GO

We can also do this with SQL Server Management Studio:

1. Initially right-click on the database name
2. Select Tasks > Backup
3. Then select “Transaction Log” as the backup type
4. Select “Disk” as the destination
5. Click on “Add…” to add a backup file and type “D:\Backups\abcd_25Oct2020.trn” and click “OK”
6. Finally, click “OK” again to create the backup

• Copy Only Backup

A copy-only backup is independent of the sequence of conventional SQL Server backups. It does not affect the sequence of the existing backup chain.

Usually, taking a backup changes the database and affects how it restore the backup. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.

There are two types of copy-only backups:

1. Copy-only full backup
2. Copy-only transaction log backup

We cannot run differential backup considering copy-only full backup cannot restore any differential backup on top of it.

A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. The transaction log is never truncated after a copy-only backup.

Copy-only backups record in the is_copy_only column of the backupset table.

Below T-SQL can be used to run copy-only backups:

–Run Copy-Only Full backup
BACKUP DATABASE abcd
TO DISK = ‘D:\BACKUP\abcd_25Oct2020_Copy.bak’
WITH COPY_ONLY;

–Run Copy-Only Log backup
BACKUP LOG abcd
TO DISK = ‘D:\Backup\abcd_25Oct2020_LogCopy.trn’
WITH COPY_ONLY;

• File Backup

A full file backup backs up all the data in one or more files or filegroups. The files in a SQL Server database can be backed up and restored individually.

Also, we can specify a whole filegroup instead of specifying each constituent file individually.

Note that if any file in a filegroup is offline (for example, because the file is being restored), the whole filegroup is offline and cannot be backed up.

A file backup can serve as the differential base for differential file backups. File backups can increase the speed of recovery and restore only damaged files, without restoring the rest of the database.

–Backup the files in the abcd_FG secondary filegroup.
BACKUP DATABASE abcd
FILE = ‘abcd_Data’,
FILE = ‘abcd_Data1’
TO DISK = ‘D:\Backups\abcd_25Oct2020_filebck.bak’;
GO

–Back up the files in abcd_FG filegroup.
BACKUP DATABASE Sales
FILEGROUP = ‘abcd_FG’
TO DISK = ‘D:\Backups\abcd_25Oct2020_filegrp.bak’;
Go

–Back up the files in abcd_FG filegroup.
BACKUP DATABASE Sales
FILEGROUP = ‘abcd_FG’
TO DISK = ‘D:\Backups\abcd_25Oct2020_filegrp.drn’;
With DIFFERENTIAL

We can perform this using SQL Server Management Studio.

1. Initially, right-click on the database name
2. Select Tasks > Backup
3. Then select either “Full” or “Differential” as the backup type
4. Select “Files and filegroups”
5. Now select the appropriate file and click “OK.
6. Select “Disk” as the destination
7. Then click on “Add…” to add a backup file and type “D:\Backups\abcd_25Oct2020.FIL” and click “OK”
8. Finally, click “OK” again to create the backup and repeat for other files

• Filegroup Backup

In addition to doing “File” backups, we can also do “Filegroup” backups which allow backup of all files that are in a particular filegroup.

By default, each database has a PRIMARY filegroup which is tied to the one data file that is created. We have an option of creating additional filegroups and then placing new data files in any of the filegroups.

The one advantage of using filegroup backups over file backups is that we can create a Read-Only filegroup. So instead of backing up the entire database all the time we can just backup the Read-Write filegroups.

A filegroup backup can complete either by using T-SQL or SSMS.

Create a SQL Server filegroup backup of the TestBackup database

For instance, consider a new database called TestBackup that has three data files and one log file. Two data files are in the PRIMARY filegroup and one file is in the ReadOnly filegroup. The code below shows how to do a filegroup backup.

T-SQL:

BACKUP DATABASE TestBackup FILEGROUP = ‘ReadOnly’
TO DISK = ‘D:\TestBackup_ReadOnly.FLG’
GO

SQL Server Management Studio:

1. Initially, right-click on the database name
2. Select Tasks > Backup
3. Then select either “Full” or “Differential” as the backup type
4. Now select “Files and filegroups”
5. Then select the appropriate filegroup and click “OK”
6. Select “Disk” as the destination
7. Click on “Add…” to add a backup file and type “D:\TestBackup_ReadOnly.FLG” and click “OK”
8. Finally, click “OK” again to create the backup and repeat for other filegroups

• Partial Backup

A partial backup resembles a full database backup, but it does not contain all the filegroups.

It includes all the read/write files in a database: the primary filegroup and any read/write secondary filegroups, and also any specified read-only files or filegroups.

We can issue a Partial backup for either a Full or Differential backup. However, we cannot use it for Transaction Log backups. Partial backups are not supported by SQL Server Management Studio or the Maintenance Plan Wizard.

–Backup Partial Full Backup
BACKUP DATABASE abcd READ_WRITE_FILEGROUPS
TO DISK = ‘D:\Backups\abcd_25Oct2020_Partial.bak’;
GO

–Backup Partial Differential Backup
BACKUP DATABASE abcd READ_WRITE_FILEGROUPS
TO DISK = ‘D:\Backups\abcd_25Oct2020_DFNPartial.drn’;
WITH DIFFERENTIAL
GO

[Stuck between the process? Contact us now!]

• Mirror backup

The mirrored backup feature is available only in SQL Server 2005 Enterprise Edition and later versions.

However, it is always a better option to execute the RESTORE VERIFYONLY command once the backups have completed. This command will help to verify whether the database backup set is complete and readable.

Let us go through the given example to understand in detail how to use the Mirrored Backup Set feature. In these examples, we are creating three backup files and writing each type of backup to the same file.

This does not need to be done this way. It creates one file per mirror backup set instead of having a lot of files in order to keep it simple.

So based on this the backup files each will have three backups as follows:

File 1 = full backup
File 2 = differential backup
File 3 = transaction log backup

The first step is to create the full backup. Here we are creating the full backup with two mirror copies.

— Take a Full Backup of Products Database Using WITH FORMAT clause
BACKUP DATABASE Products
TO DISK =’C:\DBBackup\Products_Mirror_Set_1.bak’
MIRROR TO DISK =’D:\DBBackup\Products_Mirror_Set_2.bak’
MIRROR TO DISK =’E:\DBBackup\Products_Mirror_Set_3.bak’
WITH FORMAT

Next, we create the differential backup again with two mirror copies and write it to the same backup files above.

— Take a Differential Backup of Products Database
BACKUP DATABASE Products
TO DISK =’C:\DBBackup\Products_Mirror_Set_1.bak’
MIRROR TO DISK =’D:\DBBackup\Products_Mirror_Set_2.bak’
MIRROR TO DISK =’E:\DBBackup\Products_Mirror_Set_3.bak’
WITH DIFFERENTIAL

Finally, we create the transaction log backup with two mirror copies and write to the same backup files.

— Take Transactional Log Backup of Products Database
BACKUP LOG Products
TO DISK =’C:\DBBackup\Products_Mirror_Set_1.bak’
MIRROR TO DISK =’D:\DBBackup\Products_Mirror_Set_2.bak’
MIRROR TO DISK =’E:\DBBackup\Products_Mirror_Set_3.bak’

We can then run the RESTORE VERIFYONLY command to see if the backup files are readable.

— Verify all the Mirrored database backup sets
RESTORE VERIFYONLY
FROM DISK =’C:\DBBackup\Products_Mirror_Set_1.bak’
RESTORE VERIFYONLY
FROM DISK =’D:\DBBackup\Products_Mirror_Set_2.bak’
RESTORE VERIFYONLY
FROM DISK =’E:\DBBackup\Products_Mirror_Set_3.bak’

We can mix and match the backups to use for the restore, we can restore the backups as follows:

Full Backup – from Products_Mirror_Set_2
Differential Backup – from Products_Mirror_Set_1
Transaction Backup – from Products_Mirror_Set_3

— Restore Full Backup of Products database using NORECOVERY Mode,
— the backup file used is from Mirror Set 2
RESTORE DATABASE [Products]
FROM DISK = N’D:\DBBackup\Products_Mirror_Set_2.bak’
WITH FILE = 1, NORECOVERY, STATS = 10
GO
— Restore Differential Backup of Products database using NORECOVERY Mode,
— the backup file used is from Mirror Set 1
RESTORE DATABASE [Products]
FROM DISK = N’C:\DBBackup\Products_Mirror_Set_1.bak’
WITH FILE = 2, NORECOVERY, STATS = 10
GO
— Restore Transactional Log Backup of Products database using RECOVERY Mode,
— the backup file used is from Mirror Set 3
RESTORE LOG [Products]
FROM DISK = N’E:\DBBackup\Products_Mirror_Set_3.bak’
WITH FILE = 3, RECOVERY, STATS = 10
GO

As we can see, we can create mirrored backup copies and then use the files from any set in order to complete the restore.

[Need help with SQL Server backup? We are here for you]

Conclusion

To conclude, we run SQL Server backups to safeguard our databases from user errors, media failure, hardware failure, etc. Today we saw the different types of SQL Server backups and how our Support Engineers go about with them.