SQL Server Backup Types and Key Concepts

The importance of database backup and restore can be neither underestimated nor ignored. And since your data is subject to modification, the primary task is to ensure its safety and mitigate the risks of data loss.

Introduction

Let’s start by saying that any information system depends on ensuring its correct and optimal work. Moreover, it is critical to configure its availability and fault tolerance, and these qualities depend much on each other.

We achieve fault-tolerance by the actions and settings combination at the following levels:

1. Hardware-based level.
2. Program level – the OS-level.
3. Program level – the application level.
4. Information system level – server components.
5. DBMS level – replication settings and fault-tolerance solutions.
6. Database level – backup and restore.

As we can see from the list above, fault-tolerance must be implemented at the level of the entire server as it is the last frontier before the performance loop of the entire information system with possible data loss.

This will be the first part of the series of articles dedicated to automated database backup and restore aimed at checking database integrity. To illustrate the processes and configurations, we use the MS SQL Server 2017 DBMS and the JobEmplDB database that belongs to a recruiting service.

Img.1.The database schema for a recruitment service

Let’s look upon the list of topics that we will review in this article and the ones that will follow:

1. Backup process basics.
2. Backup process implementation.
3. Database backup and restore implementation in SSMS.
4. Database backup and restore via dbForge Studio for SQL Server.

Backup Process Basics

Microsoft SQL Server allows three basic types of SQL Server backup:

1. Full backup.
2. Differential backup.
3. Transaction log backup.

Full Backup

A full backup is a backup containing all the data from the database in question, such as file sets and file groups, as well as logs to ensure data recovery.

For instance, we need to create a full compressed backup of the JobEmplDB database and save it at \\Shared\Backup\ with the checksums written and checked, and the backup itself checked. 

To do this, we execute the following script:

BACKUP DATABASE [JobEmplDB]
TO DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH DESCRIPTION = N'backup description',
   NOFORMAT,
   NOINIT,
   NAME = N'JobEmplDB-Full Database Backup',
   SKIP,
   NOREWIND,
   NOUNLOAD,
   COMPRESSION,
   STATS = 10,
   CHECKSUM,
   CONTINUE_AFTER_ERROR;
GO

DECLARE @backupSetId AS INT;

SELECT
	@backupSetId = position
FROM msdb..backupset
WHERE database_name = N'JobEmplDB'
AND backup_set_id = (SELECT
		MAX(backup_set_id)
	FROM msdb..backupset
	WHERE database_name = N'JobEmplDB');

IF (@backupSetId IS NULL)
BEGIN
	RAISERROR (N'Verify failed. Backup information for database ''JobEmplDB'' not found.', 16, 1);
END

RESTORE VERIFYONLY FROM DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

To obtain the script provided above, right-click on the JobEmplDB database, and choose Tasks > Back Up:

Img. 2. Choose the task to back up the database in SSMS

A new window will appear. On the General tab, choose Backup type > Full, and specify the backup destination:

Img. 3. Choose the full backup type and the backup destination in SSMS

On the Media Options tab, choose the following options:

Img. 4. Configuring parameters on the Media Options tab in SSMS

Here, we recommend checking all settings in the Reliability group. It minimizes the risk of making a damaged backup.

On the Backup Options tab, choose the parameters as shown on the screenshot. We always recommend creating compressed backups.

Img. 5. Configuring parameters on the Backup Options tab in SSMS

After you have configured all settings, click the Script button to generate the script:

Img. 6. The database backup script generation according to the specified settings in SSMS

Making backups reduces the load for the input-output system, but increases the CPU load. So, enable the database backup compression setting wherever applicable. Also, enable the Backup checksum setting on the MS SQL Server instance level with the script below:

EXEC sys.sp_configure N'backup compression default', N'1'
GO
EXEC sys.sp_configure N'backup checksum default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

You can do the same in the MS SQL Server instance properties, on the Database Settings tab:

Img. 7. Enabling the Compress backup and Backup checksum settings on the MS SQL Server instance level

We also recommend running all the checks applicable during the backup creation process. Also, check the ready backup as well.

Differential Backup

To clarify, a differential backup is a backup type based on the most recent full data backup of a partial database or data files or filegroups (the base copy for the differential copying). That base copy contains the odd data that have been changed during the comparison with the base copy for differential copying. A partial differential backup including the data extents changed in filegroups since the previous partial backup creation only is called a differential backup base.

To make a differential backup of the JobEmplDB database, execute the following script:

BACKUP DATABASE [JobEmplDB]
TO DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH DIFFERENTIAL,
   DESCRIPTION = N'backup description',
   NOFORMAT,
   NOINIT,
   NAME = N'JobEmplDB-Full Database Backup',
   SKIP,
   NOREWIND,
   NOUNLOAD,
   COMPRESSION,
   STATS = 10,
   CHECKSUM,
   CONTINUE_AFTER_ERROR;
GO

DECLARE @backupSetId AS INT;

SELECT
	@backupSetId = position
FROM msdb..backupset
WHERE database_name = N'JobEmplDB'
AND backup_set_id = (SELECT
		MAX(backup_set_id)
	FROM msdb..backupset
	WHERE database_name = N'JobEmplDB');

IF (@backupSetId IS NULL)
BEGIN
	RAISERROR (N'Verify failed. Backup information for database ''JobEmplDB'' not found.', 16, 1);
END

RESTORE VERIFYONLY FROM DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

Alternatively, you can perform the same steps as you did for the full backup creation with the only difference: select the Differential backup type on the General tab:

Img. 8. Choosing the differential backup type in SSMS

Transaction Log Backup

A transaction log backup is a backup of transaction logs. It includes all log entries that were absent in the previous transaction log backup (available in the full restore model only).

For instance, to back up the JobEmplDB transaction log, execute the following script:

BACKUP LOG [JobEmplDB]
TO DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH DESCRIPTION = N'backup description',
   NOFORMAT,
   NOINIT,
   NAME = N'JobEmplDB-Full Database Backup',
   SKIP,
   NOREWIND,
   NOUNLOAD,
   COMPRESSION,
   STATS = 10,
   CHECKSUM,
   CONTINUE_AFTER_ERROR;
GO

DECLARE @backupSetId AS INT;

SELECT
	@backupSetId = position
FROM msdb..backupset
WHERE database_name = N'JobEmplDB'
AND backup_set_id = (SELECT
		MAX(backup_set_id)
	FROM msdb..backupset
	WHERE database_name = N'JobEmplDB');

IF (@backupSetId IS NULL)
BEGIN
	RAISERROR (N'Verify failed. Backup information for database ''JobEmplDB'' not found.', 16, 1);
END

RESTORE VERIFYONLY FROM DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

Alternatively, you can perform the same steps as you did for the full backup creation with the only difference: select the Transaction log backup type on the General tab:

Img. 9. Choosing the Transaction log backup type in SSMS

Tail-Log Backup

A tail-log backup includes all entries absent in the previous backup (the final fragment of the log). It prevents data loss and keeps the log sequence undamaged. Restoring the SQL Server database for the most recent time suggests that we back up its tail log first. This tail is the latest copy of the backup in question, and it is necessary for the database recovery. The tail of the log backup is not required if the recovery point is in the earlier log backup. Besides, you don’t need the tail of the log backup if you transfer or replace (rewrite) the database, and you don’t need to restore it for the specific moment after the last backup creation.

In particular, to make a tail of the log backup of the JobEmplDB database, execute the following script:

BACKUP LOG [JobEmplDB]
TO DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH NO_TRUNCATE,
   DESCRIPTION = N'backup description',
   NOFORMAT,
   NOINIT,
   NAME = N'JobEmplDB-Full Database Backup',
   SKIP,
   NOREWIND,
   NOUNLOAD,
   NORECOVERY,
   COMPRESSION,
   STATS = 10,
   CHECKSUM,
   CONTINUE_AFTER_ERROR;
GO

DECLARE @backupSetId AS INT;

SELECT
	@backupSetId = position
FROM msdb..backupset
WHERE database_name = N'JobEmplDB'
AND backup_set_id = (SELECT
		MAX(backup_set_id)
	FROM msdb..backupset
	WHERE database_name = N'JobEmplDB');

IF (@backupSetId IS NULL)
BEGIN
	RAISERROR (N'Verify failed. Backup information for database ''JobEmplDB'' not found.', 16, 1);
END

RESTORE VERIFYONLY FROM DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

Optionally, carry out the same steps as you did for the transaction log backups, with the only difference: on the Media Options tab, enable Back up the tail of the log and leave the database in the restoring state in the Transaction log section:

Img. 10. Backing up the tail of the log in SSMS

Apart from the backup types mentioned above, there are several specific kinds:

Partial Backup

A partial backup contains only the data from some particular filegroups. Those involve the data from the primary filegroup, all filegroups available to read and write, and any additional read-only files specified separately.

Particularly, to make a backup of the PRIMARY filegroup of the JobEmplDB database, execute the following script:

BACKUP DATABASE [JobEmplDB] FILEGROUP = N'PRIMARY'
TO DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH DESCRIPTION = N'backup description',
   NOFORMAT,
   NOINIT,
   NAME = N'JobEmplDB-Full Database Backup',
   SKIP,
   NOREWIND,
   NOUNLOAD,
   COMPRESSION,
   STATS = 10,
   CHECKSUM,
   CONTINUE_AFTER_ERROR;
GO

DECLARE @backupSetId AS INT;

SELECT
	@backupSetId = position
FROM msdb..backupset
WHERE database_name = N'JobEmplDB'
AND backup_set_id = (SELECT
		MAX(backup_set_id)
	FROM msdb..backupset
	WHERE database_name = N'JobEmplDB');

IF (@backupSetId IS NULL)
BEGIN
	RAISERROR (N'Verify failed. Backup information for database ''JobEmplDB'' not found.', 16, 1);
END

RESTORE VERIFYONLY FROM DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND;
GO

As an alternative, you can perform the same steps as you did for the full backup with the only difference: on the General tab, select Files and filegroups in the Backup component section:

Img.11. Making a backup of the PRIMARY filegroup of the JobEmplDB database in SSMS

In the same way, you can create a partial differential backup (e.g., a differential backup for the PRIMARY filegroup only):

BACKUP DATABASE [JobEmplDB] FILEGROUP = N'PRIMARY'
TO DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH DIFFERENTIAL,
   DESCRIPTION = N'backup description',
   NOFORMAT,
   NOINIT,
   NAME = N'JobEmplDB-Full Database Backup',
   SKIP,
   NOREWIND,
   NOUNLOAD,
   COMPRESSION,
   STATS = 10,
   CHECKSUM,
   CONTINUE_AFTER_ERROR;
GO

DECLARE @backupSetId AS INT;

SELECT
	@backupSetId = position
FROM msdb..backupset
WHERE database_name = N'JobEmplDB'
AND backup_set_id = (SELECT
		MAX(backup_set_id)
	FROM msdb..backupset
	WHERE database_name = N'JobEmplDB');

IF (@backupSetId IS NULL)
BEGIN
	RAISERROR (N'Verify failed. Backup information for database ''JobEmplDB'' not found.', 16, 1);
END

RESTORE VERIFYONLY FROM DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND;
GO

Optionally, you can perform the same steps as you did to make the differential backup, with the only difference: on the General tab, choose Files and filegroups in the Backup components section:

Img. 12. Making a differential backup of the PRIMARY filegroup of the JobEmplDB database

Copy-Only Backup

A copy-only backup is a specific backup, independent from the standard SQL Server backups sequence.

In order to make a copy-only backup of the JobEmplDB database, execute the following script:

BACKUP DATABASE [JobEmplDB]
TO DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH COPY_ONLY,
   DESCRIPTION = N'backup description',
   NOFORMAT,
   NOINIT,
   NAME = N'JobEmplDB-Full Database Backup',
   SKIP,
   NOREWIND,
   NOUNLOAD,
   COMPRESSION,
   STATS = 10,
   CHECKSUM,
   CONTINUE_AFTER_ERROR;
GO

DECLARE @backupSetId AS INT;

SELECT
	@backupSetId = position
FROM msdb..backupset
WHERE database_name = N'JobEmplDB'
AND backup_set_id = (SELECT
		MAX(backup_set_id)
	FROM msdb..backupset
	WHERE database_name = N'JobEmplDB');

IF (@backupSetId IS NULL)
BEGIN
	RAISERROR (N'Verify failed. Backup information for database ''JobEmplDB'' not found.', 16, 1);
END

RESTORE VERIFYONLY FROM DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

Alternatively, perform the same steps as you did for the full backup with the only difference: on the General tab, select the Copy-only backup option:

Img. 13. Making a full copy-only backup in SSMS

Similarly, you can make a copy-only transaction log backup:

BACKUP LOG [JobEmplDB]
TO DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH COPY_ONLY,
   DESCRIPTION = N'backup description',
   NOFORMAT,
   NOINIT,
   NAME = N'JobEmplDB-Full Database Backup',
   SKIP,
   NOREWIND,
   NOUNLOAD,
   COMPRESSION,
   STATS = 10,
   CHECKSUM,
   CONTINUE_AFTER_ERROR;
GO

DECLARE @backupSetId AS INT;

SELECT
	@backupSetId = position
FROM msdb..backupset
WHERE database_name = N'JobEmplDB'
AND backup_set_id = (SELECT
		MAX(backup_set_id)
	FROM msdb..backupset
	WHERE database_name = N'JobEmplDB');

IF (@backupSetId IS NULL)
BEGIN
	RAISERROR (N'Verify failed. Backup information for database ''JobEmplDB'' not found.', 16, 1);
END

RESTORE VERIFYONLY FROM DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND;
GO

Also, you can carry out the same steps as for the making of the transaction log backup, with the only difference: on the General tab, select the Copy-only backup option:

Img. 14. Making a copy-only backup of the transaction log in SSMS

In the same way, you can make a partial copy-only database backup (for instance, for the PRIMARY filegroup only):

BACKUP DATABASE [JobEmplDB] FILEGROUP = N'PRIMARY'
TO DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH COPY_ONLY,
   DESCRIPTION = N'backup description',
   NOFORMAT,
   NOINIT,
   NAME = N'JobEmplDB-Full Database Backup',
   SKIP,
   NOREWIND,
   NOUNLOAD,
   COMPRESSION,
   STATS = 10,
   CHECKSUM,
   CONTINUE_AFTER_ERROR;

GO
DECLARE @backupSetId AS INT;

SELECT
	@backupSetId = position
FROM msdb..backupset
WHERE database_name = N'JobEmplDB'
AND backup_set_id = (SELECT
		MAX(backup_set_id)
	FROM msdb..backupset
	WHERE database_name = N'JobEmplDB');

IF (@backupSetId IS NULL)
BEGIN
	RAISERROR (N'Verify failed. Backup information for database ''JobEmplDB'' not found.', 16, 1);
END

RESTORE VERIFYONLY FROM DISK = N'\\Shared\Backup\JobEmplDB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND;
GO

Alternatively, you can perform the same steps as you did for the partial backup creation, with the only difference: on the General tab, select the Copy-only backup option:

Img. 15. Making a copy-only partial database backup

Refer to the following summary table for the backup types and their features:

Possibility / Backup type	Full	Differential	Transaction log
Partial (selected files and filegroups)	Supported	Supported	Not supported
Copy-only	Supported	Not supported	Supported
A tail of the log	Not supported	Not supported	Supported

Additionally, it is essential to organize the system databases’ backup and restore process. Note that it gathers all the changes made during the backup process until it is complete.

You can learn the information about the database files by running the query below:

SELECT [file_id],
	   [file_guid],
	   [type],
	   [type_desc],
	   [data_space_id],
	   [name],
	   [physical_name],
	   [state],
	   [state_desc],
	   [size],
	   [max_size],
	   [growth],
	   [is_media_read_only],
	   [is_read_only],
	   [is_sparse],
	   [is_percent_growth]
FROM sys.master_files
WHERE [database_id]=DB_ID('JobEmplDB');

The result of the query:

Img. 16. Information about the JobEmplDB database files

This query uses the sys.master_files system view and outputs the following fields:

1. file_id – the file ID in the database. The file_id parameter to the primary file always equals 1.
2. file_guid – the unique file ID.
3. type – the file type (0 – data, 1- transaction log, 2-FILESTREAM).
4. type_desc – the file type description (ROWS – data, LOG – transaction log, FILESTREAM).
5. data_space_id – the ID of the data space where this file belongs. The data space is a filegroup (0 – for transaction logs).
6. name – the logical file name in the database.
7. physical_name – the file’s full name in the operating system.
8. state – the file status:
   1. 0 – ONLINE
   2. 1 – RESTORING
   3. 2 – RECOVERING
   4. 3 – RECOVERY_PENDING
   5. 4 – SUSPECT
   6. 6 – OFFLINE
   7. 7 – DEFUNCT
9. state_desc – the data state description.
10. size – the current file size, in 8 Kb pages. For the database snapshot, the size argument defines the maximum space that the snapshot can use for the file only.
11. max_size – the maximum file size in 8Kb-pages (0 – prohibits to increase size, -1 – the file size may increase until it fills the full drive).
12. growth (0 – the size of the file is fixed and won’t increase; >0 – the file will grow automatically):
    1. If the is_percent_growth argument has the value 0, the growth step is measured in 8 KB-pages, rounded to the closest 64 KB.
    2. If the is_percent_growth argument is 1, the size growth step is expressed in the overall size percent.
13. is_media_read_only (1 – file if on the read-only drives, 0 – file is on the drive that allows reading and writing).
14. is_read_only ( 1 – the file is marked as read-only, 0 – the file is marked as available to read and write).
15. is_sparse (1 – the sparse file, 0 – not a sparse file).
16. is_percent_growth (1 – the file size increases in percent, 0 – the size increases absolutely, in pages).

We can get this information with the sys.database_files system view:

SELECT [file_id],
	   [file_guid],
	   [type],
	   [type_desc],
	   [data_space_id],
	   [name],
	   [physical_name],
	   [state],
	   [state_desc],
	   [size],
	   [max_size],
	   [growth],
	   [is_media_read_only],
	   [is_read_only],
	   [is_sparse],
	   [is_percent_growth]
FROM sys.database_files;

While working with the database, it is crucial to check its status and condition with the below query:

SELECT
   [user_access]
   ,[user_access_desc]
   ,[is_read_only]
   ,[state]
   ,[state_desc]
   ,[is_in_standby]
FROM sys.databases
WHERE [name] = 'JobEmplDB';

The query result is as shown on the screenshot below:

Img. 17. Getting the information about the database condition

The query uses the sys.databases system view and outputs the following fields:

1. user_access – the user access setting:
   1. 0 – MULTI_USER
   2. 1 – SINGLE_USER
   3. 2 – RESTRICTED_USER
2. user_access_desc – the user access task description.
3. is_read_only (1 – the database is read-only, 0 – the database is available to read and write).
4. state – the database status:
   1. 0 – ONLINE
   2. 1 – RESTORING
   3. 2 – RECOVERING
   4. 3 – RECOVERY_PENDING
   5. 4 – SUSPECT
   6. 5 – EMERGENCY
   7. 6 – OFFLINE
   8. 7 – COPYING
   9. 10 – OFFLINE_SECONDARY
5. state_desc – the database state description.
6. is_in_standby – 1 – the database is available to read and the restore log only.

To get the information about the last 10 database backups of each type (full, differential, of transaction log), execute the below script:

;WITH backup_cte
AS
(SELECT
		bs.[database_name]
	   ,CASE bs.[type]
			WHEN 'D' THEN 'database'
			WHEN 'L' THEN 'log'
			WHEN 'I' THEN 'differential'
			WHEN 'F' THEN 'filegroup'
			WHEN 'G' THEN 'diff file'
			WHEN 'P' THEN 'partial'
			WHEN 'Q' THEN 'partial differential'
			ELSE 'other'
		END AS [backup_type]
	   ,bs.[first_lsn]
	   ,bs.[last_lsn]
	   ,bs.[backup_start_date]
	   ,bs.[backup_finish_date]
	   ,CAST(bs.[backup_size] AS DECIMAL(18, 3)) / 1024 / 1024 AS [BackupSizeMb]
	   ,ROW_NUMBER() OVER
		(
		PARTITION BY bs.[database_name],
		bs.[type]
		ORDER BY bs.[backup_finish_date] DESC
		) AS [rownum]
	   ,bmf.[logical_device_name] AS [LogicalDeviceName]
	   ,bmf.[physical_device_name] AS [PhysicalDeviceName]
	   ,bs.[server_name]
	   ,bs.[user_name]
	FROM msdb.dbo.backupset AS bs
	INNER JOIN msdb.dbo.backupmediafamily AS bmf
		ON [bs].[media_set_id] = [bmf].[media_set_id])
SELECT
	[server_name] AS [ServerName]
   ,[database_name] AS [DBName]
   ,[user_name] AS [UserName]
   ,[backup_type] AS [BackupType]
   ,[backup_start_date] AS [BackupStartDate]
   ,[backup_finish_date] AS [BackupFinishDate]
   ,[BackupSizeMb]
   ,[LogicalDeviceName]
   ,[PhysicalDeviceName]
   ,[first_lsn] AS [FirstLSN]
   ,[last_lsn] AS [LastLSN]
   ,[rownum]
FROM backup_cte
WHERE [database_name] = 'JobEmplDB'
AND [rownum] <= 10
ORDER BY [rownum] ASC;

The query execution result is as follows:

Img. 18. Getting information about the JobEmplDB database backups

The current query uses the msdb.dbo.backupset and msdb.dbo.backupmediafamily system views and outputs the following fields:

1. ServerName is the name of the server, processing the SQL Server backup operation.
2. DBName is the name of the database that participates in the backup operation.
3. UserName is the name of the user who runs the backup operation.
4. BackupType is the backup type (database, differential, log, filegroup, diff file, partial, partial differential).
5. BackupStartDate is the backup process start date.
6. BackupFinishDate is the backup process end date.
7. BackupSizeMb is the uncompressed backup size in MB.
8. LogicalDeviceName is the logical name of the backup device.
9. PhysicalDeviceName is the physical name of the backup device.
10. FirstLSN is the registration number of the first or the earliest transaction log entry in the reserved data set.
11. LastLSN is the registration number of the next log entry after the reserved data set. 
12. rownum is the backup order number that starts from 1 for each unique set (the database name, the backup type).

Similarly, we can get the most recent database backups for each backup type:

;WITH backup_cte
AS
(SELECT
		bs.[database_name]
	   ,CASE bs.[type]
			WHEN 'D' THEN 'database'
			WHEN 'L' THEN 'log'
			WHEN 'I' THEN 'differential'
			WHEN 'F' THEN 'filegroup'
			WHEN 'G' THEN 'diff file'
			WHEN 'P' THEN 'partial'
			WHEN 'Q' THEN 'partial differential'
			ELSE 'other'
		END AS [backup_type]
	   ,bs.[first_lsn]
	   ,bs.[last_lsn]
	   ,bs.[backup_start_date]
	   ,bs.[backup_finish_date]
	   ,CAST(bs.[backup_size] AS DECIMAL(18, 3)) / 1024 / 1024 AS [BackupSizeMb]
	   ,ROW_NUMBER() OVER
		(
		PARTITION BY bs.[database_name],
		bs.[type]
		ORDER BY bs.[backup_finish_date] DESC
		) AS [rownum]
	   ,bmf.[logical_device_name] AS [LogicalDeviceName]
	   ,bmf.[physical_device_name] AS [PhysicalDeviceName]
	   ,bs.[server_name]
	   ,bs.[user_name]
	FROM msdb.dbo.backupset AS bs
	INNER JOIN msdb.dbo.backupmediafamily AS bmf
		ON [bs].[media_set_id] = [bmf].[media_set_id])
SELECT
	[server_name] AS [ServerName]
   ,[database_name] AS [DBName]
   ,[user_name] AS [UserName]
   ,[backup_type] AS [BackupType]
   ,[backup_start_date] AS [BackupStartDate]
   ,[backup_finish_date] AS [BackupFinishDate]
   ,[BackupSizeMb]
   ,[LogicalDeviceName]
   ,[PhysicalDeviceName]
   ,[first_lsn] AS [FirstLSN]
   ,[last_lsn] AS [LastLSN]
   ,[rownum]
FROM backup_cte
WHERE [database_name] = 'JobEmplDB'
AND [rownum] = 1
ORDER BY [rownum] ASC;

View the query result below:

Img. 20. Getting the data about the JobEmplDB database last backups

SQL Server also supports the Volume Shadow Copy (VSS) by providing the writer module (SQL module writer) for the third-party backup device to use the VSS platform to make the backups of the database files. For more information, refer to this link.

Besides, if you are looking for a tool that provides for quick and efficient database backup and restore, check out dbForge Studio for SQL Server, which has a powerful Backup Wizard.

Conclusion

To sum up, we have reviewed the basic notions of the SQL Server backup, made a detailed overview of the main SQL Server backup types, and exemplified the procedures with the JobEmplDB database. To find out more about Backup process implementation, read our next article dedicated to this topic!