Create a Transact-SQL snapshot backup
Applies to:
SQL Server 2022 (16.x)
This article explains what, why, and how to use Transact-SQL snapshot backups. Transact-SQL (T-SQL) snapshot backups were introduced in SQL Server 2022 (16.x).
Databases are getting larger and larger every day. Traditionally, SQL Server backups are streaming backups. A streaming backup depends on the size of the database. Backup operations consume resources (CPU, memory, I/O, network) which affect throughput of the concurrent OLTP workload during the backup. One way to make the backup performance constant, rather than depend on the size of data, is by performing a snapshot backup using mechanisms provided by the underlying storage hardware or service.
Because the backup itself happens at the hardware level, this feature isn't a pure SQL Server solution. SQL Server must first prepare the data and log files for the snapshot, so that the files are guaranteed to be in a state that can later be restored. Once this step is complete, write operations are paused on SQL Server (read requests are still allowed), and control is handed over to the backup application to complete the snapshot. Once the snapshot is successfully complete, the application must return control back to SQL Server where write operations are then resumed.
Because we must freeze write operations during the snapshot operation, it's essential that the snapshot happens quickly, so that the workload on the server isn't interrupted for an extended period. In the past, users relied on non-Microsoft solutions that were built on top of the SQL Writer service to complete snapshot backups. The SQL Writer service depends on Windows VSS (Volume Shadow Service) along with SQL Server VDI (Virtual Device Interface) to perform the orchestration between SQL Server and the disk-level snapshot.
Backup clients based on the SQL Writer service tend to be complex, and they only work on Windows. With T-SQL snapshot backups, the SQL Server side of the orchestration can be handled with a series of T-SQL commands. This functionality allows users to create their own small backup applications that can run on either Windows or Linux, or even scripted solutions if the underlying storage supports a scripting interface to initiate a snapshot.
Here's a sample PowerShell script, which demonstrates an end-to-end solution of backing up and restoring a database in an Azure SQL IaaS Virtual Machine. The sample uses the T-SQL snapshot backup capabilities introduced in SQL Server 2022 (16.x).
Workflow
The T-SQL snapshot backup syntax decouples the vendor-dependent snapshot mechanism from the suspend and backup operations. With this syntax, you can:
Freeze a database with the
ALTER
command, which provides an opportunity for you to perform the snapshot of the underlying storage. After which, you can thaw the database and record the snapshot with theBACKUP
command.Perform snapshots of multiple databases simultaneously with the new
BACKUP GROUP
andBACKUP SERVER
commands. With this option, snapshots can be performed at the snapshot granularity of the underlying storage, eliminating the need to perform a snapshot of the same disk multiple times.Perform
FULL
backups andCOPY_ONLY FULL
backups. These backups are recorded inmsdb
as well.Perform point-in-time recovery using log backups taken with the normal streaming approach after the snapshot
FULL
backup. Streaming differential backups are also supported if desired.
Note
Differential bitmaps are cleared during the first stage when suspending the database with the ALTER
command. If the user decides to thaw the database without performing a backup because the snapshot failed or for any other reason, the differential bitmap is invalid. Any subsequent differential backups are more I/O intensive, as they must scan the whole database to do the differential backup. The differential bitmap becomes valid again after a successful snapshot backup.
The following diagram illustrates the high-level workflow of T-SQL snapshot backups:
The middle snapshot step requires you to initiate the snapshot on the underlying storage. The following diagram shows an example of how a backup script could work with SQL Server to complete the snapshot backup process:
Similarly, a restore script could work as follows:
Limitations
The maximum number of databases you can back up with this feature is 64. If there are more than 64 databases on the server, you see the following error:
Error message:
Msg 925, Level 19, State 1, Line 4
Maximum number of databases used for each query has been exceeded. The maximum allowed is 64.
Examples
The following sections show different T-SQL commands used to perform snapshot backup to disk. When a snapshot backup is written to disk, only the metadata connected to the snapshot backup is written to the file. The output doesn't contain any of the database contents except for the header and the file contents. The shell file created as part of performing snapshot backup should be used with the actual snapshot URI to make a complete backup. A RESTORE
of a database from this file requires the user to copy the database files from the snapshot URI to the mount point, before issuing the RESTORE
command. Users are able to run all the traditional T-SQL commands, like RESTORE HEADERONLY
and RESTORE FILELISTONLY
, on this snapshot backup metadata file, along with RESTORE DATABASE
. The syntax supports writing snapshot backup metadata to DISK
or URL
. The snapshot backup sets can also be appended just like streaming backup sets into a single file.
Note
For backup to URL, block blobs are preferred, although page blobs are supported for SQL Server on Windows. For SQL Server on Linux and containers, only block blobs are supported.
A. Suspend a single user database for snapshot backup and record a database backup
ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
BACKUP DATABASE testdb1
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
B. Suspend multiple user databases for snapshot backup
If multiple databases are on the same underlying disk, you can suspend multiple databases with the following command.
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));
BACKUP GROUP testdb1, testdb2
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
C. Suspend all user databases on the server for snapshot backup
If all the user databases on the server need to be suspended, use the following command.
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
BACKUP SERVER
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Note
None of these commands support suspending system databases (master
, model
, and msdb
) for snapshot backup.
D. Suspend multiple user databases with a single command
Record a snapshot of all the user databases on the server into a single backup set:
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));
BACKUP GROUP testdb1, testdb2
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Note
By default, SUSPEND_FOR_SNAPSHOT_BACKUP
commands clear the differential bitmap. If you prefer to perform a copy only backup, use the COPY_ONLY
keyword as shown in the following examples.
E. Perform copy-only snapshot backups
Since the differential bitmap is cleared before the freeze, SUSPEND_FOR_SNAPSHOT_BACKUP
provides an option (COPY_ONLY
) not to clear the differential bitmap before the freeze.
ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);
BACKUP DATABASE testdb1
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2), MODE = COPY_ONLY);
BACKUP GROUP testdb1, testdb2
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);
BACKUP SERVER
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Note
It isn't necessary to use COPY_ONLY
on the BACKUP
command, as it's already specified when suspending the database for snapshot backup.
F. Back up a database with data and log files on different drives
If you have a database with data files (.mdf
and .ndf
) across multiple drives, and the transaction log file (.ldf
) on a different drive, you can perform a snapshot backup as follows:
Suspend the database (which freezes the write I/O on both data and log files).
ALTER SERVER CONFIGURATION SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
Take a snapshot of all the underlying disks where the database data and log files are present. This step is hardware dependent.
Perform the backup using the
METADATA_ONLY
option, which creates the output containing the snapshot backup metadata (.bkm
).BACKUP DATABASE testdb1 TO DISK = 'D:\Temp\db.bkm' WITH METADATA_ONLY;
To restore this backup at a later stage, follow these steps:
Mount or attach the snapshot disks on the VM where you want to restore.
Use the
.bkm
file (from step 3 in the previous list) when you perform a database restore.If the drives are different during restore, use the
MOVE
option for the logical files to place them in the required destination. For an example, see Example N.
G. Tag the backupset
You can use the MEDIANAME
and MEDIADESCRIPTION
options in the backup command to tag the URI associated with the snapshot. This use allows the backup file to carry the underlying snapshot information along with the database metadata. You can also use the NAME
and DESCRIPTION
options to tag the URI with the individual backupset snapshot.
SQL Server doesn't interpret the LABEL
information in any way. It does however help the user to view the URI associated with the snapshot backup with the RESTORE LABELONLY
command.
You could then attach the snapshot disks located at the URI to the VM to restore the snapshot. The snapshot URI stored in the MEDIANAME
and MEDIADESCRIPTION
is also then available for viewing in the msdb
database table dbo.backupmediaset
.
H. Output of snapshot backup with RESTORE HEADERONLY
The output with RESTORE HEADERONLY
looks like the following sample, if the database, group, and server are executed in sequence and written to the same output file:
RESTORE HEADERONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;
I. Output of snapshot backup with RESTORE FILELISTONLY
The output with RESTORE FILELISTONLY
displays the first backup set by default:
RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;
J. Filter RESTORE FILELISTONLY output to a backup set
To specifically select a certain backup set from multiple backup sets with RESTORE FILELISTONLY
, use the FILE
clause that is already supported on RESTORE FILELISTONLY
.
RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3;
K. Filter RESTORE FILELISTONLY output to a database
To further select a single database from multiple databases within the selected backup set with RESTORE FILELISTONLY
, use the FILE
clause with the DBNAME
clause. The DBNAME
clause can be used only on snapshot backup sets.
RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3';
L. Restore a snapshot database
Restoring a database from snapshot backup is like attaching a database. Run the restore command without the RECOVERY
option, if the database needs to be attached without recovery. By default, RESTORE
selects the first database in the snapshot backup set. The following example restores testdb1
. If testdb1
already exists on the server, include the REPLACE
clause. You need to mount the database files before you run RESTORE
.
RESTORE DATABASE testdb1
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, REPLACE, --> no DBNAME clause - restore first database in backup set
MOVE 'testdb1' TO 'D:\Temp\snap\testdb1.mdf',
MOVE 'testdb1_log' TO 'D:\Temp\snap\testdb1_log.ldf';
M. Restore a snapshot database listed in the middle
If the database that needs to be RESTORED
is in the middle, specify the database to be restored with the DBNAME
clause. The following syntax restores the specified database in the DBNAME
clause.
RESTORE DATABASE testdb3
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> restores testdb3 database
MOVE 'testdb3' TO 'D:\Temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'D:\Temp\snap\testdb3_log.ldf',
NORECOVERY;
N. Restore the database with a different name
You can restore the database with a different name. If the database that needs to be RESTORED
is in the middle, specify the database to be restored with the DBNAME
clause. The following syntax restores the specified database with the DBNAME
clause and renames it to testdb33
.
RESTORE DATABASE testdb33 --> renames the specified database testdb3 to testdb33.
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> original name specified here
MOVE 'testdb3' TO 'D:\Temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'D:\Temp\snap\testdb3_log.ldf',
NORECOVERY;
O. Use RESTORE BACKUPSETONLY to extract databases from a backup set containing multiple databases
A snapshot backup set containing multiple databases from a group or server snapshot can be split with the RESTORE BACKUPSETONLY
command. This command produces one backup set per database.
If a server snapshot contains three databases in a backup file containing a single backup set, the following command generates three backup sets, one for each database. It creates a directory with <file_name_prefix>_<unique_time_stamp>
for the output files.
RESTORE BACKUPSETONLY
FROM DISK = 'D:\Temp\db1.bkm'
WITH METADATA_ONLY;
P. Use RESTORE BACKUPSETONLY to extract a specific database in a backup set containing multiple databases
RESTORE BACKUPSETONLY
supports the DBNAME
parameter if the user wants to output one database out of the three databases in the backup set. It also supports the FILE
parameter to filter multiple backup sets in the backup file.
RESTORE BACKUPSETONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb2';
Q. Monitor the suspend status and locks acquired
You can use the following dynamic management views (DMVs):
sys.dm_server_suspend_status
(view the suspend status)sys.dm_tran_locks
(view the locks acquired)
R. List backupset details
The following sample script lists backupset information for Transact-SQL snapshot backups.
SELECT database_name,
type,
backup_size,
backup_start_date,
backup_finish_date,
is_snapshot
FROM msdb.dbo.backupset
WHERE is_snapshot = 1;
S. Check if a database was suspended for snapshot backup
The following sample script outputs database level properties for databases that are suspended for snapshot backup.
SELECT SERVERPROPERTY('SuspendedDatabaseCount');
SELECT SERVERPROPERTY('IsServerSuspendedForSnapshotBackup');
SELECT DATABASEPROPERTYEX('db1', 'IsDatabaseSuspendedForSnapshotBackup');
T. Sample T-SQL troubleshooting script
The following sample script detects suspended databases on the server, and unsuspends them if necessary.
IF (SERVERPROPERTY('IsServerSuspendedForSnapshotBackup') = 1)
BEGIN
--full server suspended, requires server level thaw
PRINT 'Full server is suspended, requires server level thaw'
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF
END
ELSE
BEGIN
IF (SERVERPROPERTY('SuspendedDatabaseCount') > 0)
BEGIN
DECLARE @curdb SYSNAME
DECLARE @sql NVARCHAR(500)
DECLARE mycursor CURSOR FAST_FORWARD
FOR
SELECT db_name
FROM sys.dm_server_suspend_status;
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @curdb
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'unfreezing DB ' + @curdb
SET @sql = 'ALTER DATABASE ' + @curdb + ' SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF'
EXEC sp_executesql @SQL
FETCH NEXT
FROM mycursor
INTO @curdb
END
PRINT 'All DB unfrozen'
CLOSE mycursor;
DEALLOCATE mycursor;
END
ELSE
-- no suspended database, thus no user action needed.
PRINT 'No database/server is suspended for snapshot backup'
END
Related content
Atsiliepimai
https://aka.ms/ContentUserFeedback.
Jau greitai: 2024 m. palaipsniui atsisakysime „GitHub“ problemų, kaip turiniui taikomo atsiliepimų mechanizmo, ir pakeisime jį nauja atsiliepimų sistema. Daugiau informacijos žr.Pateikti ir peržiūrėti atsiliepimą, skirtą