Thursday, November 21, 2024
HomeProductsSQL Server ToolsSQL Server Backup Types and Key Concepts
How ToSQL Server Tools

SQL Server Backup Types and Key Concepts

dbForge Team
By dbForge Team
0
9837

The importance of regular database backup and recovery 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.

That’s why we have database backups at hand—and in this article, we’ll guide you through main types and key concepts of backups as related to SQL Server databases.

Introduction

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

Fault tolerance can be achieved at the following levels:

  1. Hardware 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 recovery

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

This will be the first part of the series of articles dedicated to automated database backup and recovery focused on ensuring database integrity. To demonstrate the processes and configurations, we will use Microsoft SQL Server as our DBMS and a JobEmplDB database that belongs to a recruitment service.

Now let’s take a closer look at the backup types in SQL Server.

Backup types

In our overview, we’ll cover the following backup types:

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 against our database.

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 above-mentioned script in SSMS, we go to Object Explorer, right-click the JobEmplDB database, and select Tasks > Back Up from the shortcut menu.

The Back Up Database window appears. On the General page, we set the Backup type to Full and specify the backup destination.

On the Media Options page, we select the options as follows.

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

On the Backup Options page, we configure the options as shown in the following screenshot. We always recommend creating compressed backups.

Finally, we can click Script to generate the script.

Making backups reduces the load on the input-output system, but increases the CPU load. Thus, you should enable the database backup compression setting wherever applicable. Also, you can enable the Backup checksum setting on the SQL Server instance level using the following script.

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 SQL Server instance properties, on the Database Settings page.

We also recommend running all the checks during the creation of your backup. You should also check the completed backup as well.

Full backup to multiple files

In case our database is huge, we may just as well split our backup to multiple smaller files for further convenience. To do that, we need to specify multiple destination files in our script, similarly to the following.

BACKUP DATABASE JobEmplDB
TO DISK = N'\\Shared\Backup\JobEmplDB_Part1.bak',
   DISK = N'\\Shared\Backup\JobEmplDB_Part2.bak',
   DISK = N'\\Shared\Backup\JobEmplDB_Part3.bak'
WITH INIT, FORMAT, STATS = 10;

Differential backup

A differential backup is basically a superset of the latest full backup and contains all changes that have been made since the said latest full backup took place. Thus, fewer changes mean a smaller backup size, and vice versa.

In our case, to make a differential backup of the JobEmplDB database, we 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, we can do it via Back Up Database in SSMS, taking the same steps as in the case of full backup, with the only difference: we select the Differential backup type on the General page.

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 SQL Server full recovery model only).

For instance, to back up the JobEmplDB transaction log, we 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, we can do it via the same Back Up Database in SSMS. This time we need to select the Transaction Log backup type on the General page.

Tail-log backup

A tail-log backup includes all entries absent from 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 database recovery. The tail-log backup is not required if the recovery point is in the earlier log backup. Besides, it is not necessary to take a tail-log backup if you transfer or replace (rewrite) the database, and you don’t need to restore it to a specific version after the latest backup.

In our case, to make a tail-log backup of the JobEmplDB database, we 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

A tail-log backup can be just as well made using Back Up Database in SSMS. To do that, we go to the Media Options page and select Back up the tail of the log and leave the database in the restoring state under Transaction log.

Partial backup

A partial backup is useful in case you want to exclude read-only filegroups. For a read-write database, a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains only the primary filegroup.

Here’s an example of a partial backup that uses a parameter called READ_WRITE_FILEGROUPS in order to back up read-write filegroups only.

BACKUP DATABASE [JobEmplDB]
READ_WRITE_FILEGROUPS
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS02\MSSQL\Backup\JobEmplDB_partial.bak'
WITH DESCRIPTION = N'Partial Backup of read-write filegroups',
   NOFORMAT,
   NOINIT,
   NAME = N'JobEmplDB-Partial Backup',
   SKIP,
   NOREWIND,
   NOUNLOAD,
   STATS = 10;
GO

Note that partial backups are not supported by SQL Server Management Studio.

File and file group backup

You can as well back up data from selected files and/or filegroups. In our case, we’ll make a backup of the PRIMARY filegroup of the JobEmplDB database. To do that, we run 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

Alternatively, this can be done using Back Up Database in SSMS. This time, on the General tab, we select the PRIMARY filegroup in Files and filegroups under Backup component.

In the same way, we 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

To do that in Back Up Database, we select the differential backup type and the PRIMARY filegroup.

Copy-only backup

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

In order to make a copy-only backup of the JobEmplDB database, we 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, we can do it via Back Up Database—and on the General page, we select the Copy-only backup checkbox.

Similarly, we 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

And again, when we do it via Back Up Database, we select the Copy-only backup checkbox.

In the same way, we can make a copy-only partial backup for the PRIMARY filegroup.

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

When we do it via Back Up Database, the Copy-only backup checkbox is again there to help us.

That’s it! Refer to the following summary table for the backup types and their features.

Backup types Full Differential Transaction log
Partial (selected files and filegroups) Supported Supported Not supported
Copy-only Supported Not supported Supported
Tail-log Not supported Not supported Supported

Additionally, it is essential to organize the back up and restore process for system databases. Note that it includes all the changes made while the backup process is underway.

We can learn more about the database files by running the following query.

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');

Here is the output for our case.

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 full file 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 a 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 8 kB 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 – the file is on a read-only drive; 0 – the file is on a 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 for reading and writing).
  15. is_sparse (1 – a sparse file; 0 – not a sparse file).
  16. is_percent_growth (1 – the file size increases in terms of percentage; 0 – the file size increases page by page).

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 a database, it is crucial to check its status and condition with the following query.

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

Here is the output for our case.

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, or transaction log), we execute the following 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 output is as follows.

Our 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 that runs 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;

Here is the query output.

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 backups of database files. For more information, refer to SQL Server Backup Applications – Volume Shadow Copy Service (VSS) and SQL Writer.

Besides, if you are looking for a tool that provides for quick and efficient database backup and recovery, you may as well check out dbForge Studio for SQL Server, which offers powerful wizard-aided backup and recovery.

Conclusion

To sum up, we have reviewed the basic notions of the SQL Server backup, made a detailed overview of the main backup types, and illustrated each case with our JobEmplDB database. Now you can check our next article, dedicated to setting up daily auto-backups of SQL Server databases.

dbForge Team
dbForge Team
RELATED ARTICLES

Whitepaper

Social

Topics

How To454What’s New318Events77Industry Insights36

Products

SQL Server Tools365MySQL Tools183ADO.NET Data Providers95PostgreSQL Tools90Oracle Tools87Delphi DAC79ODBC Drivers70Productivity Tools58ORM Solutions47SSIS Components24Excel Add-ins20

Devart

Founded in 1997, Devart is currently one of the leading developers of database management software, ALM solutions and data providers for most popular database servers. © 2024 Devart. All rights reserved.

Products
Support
Tutorials

Company