Australian cloud for business software — built and run in Sydney since 2003

+61 2 8282 5656
support@netleverage.com
Talk to an engineer
Talk to an engineer
NetLeverage
NetLeverage

SQL Server 2022 T-SQL snapshot backups: what the small .bkm file does and does not hold

Technical article
Backup & Recovery

Originally published:

About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

Large SQL Server databases take a long time to back up by streaming every page to a file. SQL Server 2022 added a way to let the storage do the heavy lifting: SQL Server pauses writes, the storage takes a snapshot of the disks, and SQL Server records that snapshot as a proper full backup.

The result is a backup file of a few kilobytes that SQL Server treats as a full backup, and a storage snapshot that holds the actual data. Recovery depends on both. This guide explains how the sequence works, what is recorded where, how a restore uses it, and what to check without changing anything.

Why it matters

Microsoft's page on the feature explains the motivation: "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." A snapshot backup aims to make backup time constant rather than proportional to size.

The recovery risk is that the metadata file looks like a backup and is recorded in msdb as one, but holds none of the data (our reading). If the storage snapshot is deleted by a retention policy, lost with the array, or cannot be identified, the metadata file cannot bring the database back on its own.

How it differs from VSS-based snapshot backups

Before SQL Server 2022, storage snapshot backups ran through Windows. Microsoft describes it this way: "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." It adds: "Backup clients based on the SQL Writer service tend to be complex, and they only work on Windows."

With the Transact-SQL route, "the SQL Server side of the orchestration can be handled with a series of T-SQL commands." Microsoft says this lets users "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."

It is also different from a SQL Server database snapshot, which is a read-only copy inside the instance. The RESTORE documentation is clear that "Reverting from a database snapshot is not intended for media recovery."

The three-step sequence

Microsoft describes the feature as decoupling "the vendor-dependent snapshot mechanism from the suspend and backup operations." Its workflow has three parts.

Illustrative, untested lab sequence only. Suspending a database freezes writes and can interrupt its workload. Before any authorised isolated trial, have the DBA and storage specialist review the entire suspend/snapshot/record sequence and its failure handling, including a bounded timeout and correctly scoped thaw if snapshot or recording fails. Confirm suspension has cleared; do not report a failed snapshot as a successful backup. Use only disposable synthetic databases, isolated test volumes and a unique new metadata-file path. The vendor examples use FORMAT, which can overwrite existing backup media; it is omitted below. Never reuse a last-good backup destination for this exercise.

1. Suspend. For one database:

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

Or for a group, or every user database, with ALTER SERVER CONFIGURATION. The page explains what happens: "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."

2. Snapshot. The backup application tells the storage to take the snapshot. This step is outside SQL Server: "Because the backup itself happens at the hardware level, this feature isn't a pure SQL Server solution." For a database whose data and log files sit on several drives, the page says to "Take a snapshot of all the underlying disks where the database data and log files are present. This step is hardware dependent."

3. Record the backup. The BACKUP command with METADATA_ONLY writes the metadata file and resumes writes:

BACKUP DATABASE testdb1
TO DISK = 'D:\IsolatedSqlLab\unique-snapshot-run-001.bkm'
WITH METADATA_ONLY;

The BACKUP reference states that METADATA_ONLY is "Required for snapshot backup" and is "synonymous with SNAPSHOT". BACKUP GROUP and BACKUP SERVER back up a group or all databases and each "Requires WITH METADATA_ONLY".

Because writes are frozen between steps 1 and 3, Microsoft warns that "it's essential that the snapshot happens quickly, so that the workload on the server isn't interrupted for an extended period."

What the .bkm file holds

The documentation is explicit: "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." It continues: "The shell file created as part of performing snapshot backup should be used with the actual snapshot URI to make a complete backup."

In other words, a complete backup is the pair: the .bkm file plus the storage snapshot it refers to (as the documentation describes). Microsoft suggests a way to keep the two linked: use "the MEDIANAME and MEDIADESCRIPTION options in the backup command to tag the URI associated with the snapshot." It notes that "SQL Server doesn't interpret the LABEL information in any way", but the tag can be read back with RESTORE LABELONLY and is "available for viewing in the msdb database table dbo.backupmediaset."

If those tags are left empty, the only link between the metadata file and the snapshot may be the time and the operator's memory (our reading). Tagging every snapshot backup with the snapshot's identifier is worth treating as mandatory (our recommendation).

Backup chain effects

The feature records real full backups. Microsoft lists that you can "Perform FULL backups and COPY_ONLY FULL backups. These backups are recorded in msdb as well." It also says you can "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."

Two consequences follow.

Differential bitmaps. The page notes: "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." Later differentials "are more I/O intensive, as they must scan the whole database". The bitmap "becomes valid again after a successful snapshot backup." MODE = COPY_ONLY on the suspend avoids clearing it.

Other backup tools. A non-copy-only snapshot backup becomes the base for differentials (our understanding of how full backups work generally). If another tool also takes full backups, check which full backup each differential depends on before relying on it (our recommendation).

Limits Microsoft documents

  • Version: SQL Server 2022 (16.x) and later.
  • Database count: "The maximum number of databases you can back up with this feature is 64." Beyond that, error 925 is returned.
  • System databases: "None of these commands support suspending system databases ( master , model , and msdb ) for snapshot backup." Those still need ordinary backups (our reading).
  • Storage: the snapshot must cover every disk holding the database's data and log files, and its consistency depends on the storage taking it while SQL Server is suspended (our understanding).

How a restore uses it

The page says: "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." For the multi-drive example: "Mount or attach the snapshot disks on the VM where you want to restore", then "Use the .bkm file … when you perform a database restore", and if the drives differ, "use the MOVE option for the logical files".

It describes restore as being "like attaching a database". The RESTORE reference shows applying a log after the snapshot full backup:

RESTORE DATABASE Sales
 FROM DISK = 'D:\MSSQL\Backup\SalesSnapshotFull.bkm'
 WITH METADATA_ONLY,
 NORECOVERY;
RESTORE LOG Sales
 FROM DISK = 'D:\MSSQL\Backup\SalesLog.trn'
 WITH RECOVERY;

These are restore commands, shown for understanding; run them only in a rehearsal environment.

Read-only checks

These queries read catalogue, history or file headers and change nothing. Run them in SQL Server Management Studio or sqlcmd (Transact-SQL). Record the results and any permission errors, which mean the picture is incomplete.

Is anything suspended right now?

SELECT SERVERPROPERTY('SuspendedDatabaseCount');
SELECT SERVERPROPERTY('IsServerSuspendedForSnapshotBackup');
SELECT * FROM sys.dm_server_suspend_status;

Microsoft describes SuspendedDatabaseCount as "The number of suspended databases on the server." A non-zero value outside a backup window suggests a script stopped between suspend and backup (our reading). Microsoft's own troubleshooting sample thaws such databases; that sample changes state and belongs in your incident procedure, not in a read-only check.

Which snapshot backups are recorded, and what did they tag?

SELECT b.database_name, b.type, b.backup_start_date, b.backup_finish_date,
       b.is_copy_only, m.name AS media_name, m.description AS media_description
FROM msdb.dbo.backupset AS b
JOIN msdb.dbo.backupmediaset AS m ON m.media_set_id = b.media_set_id
WHERE b.is_snapshot = 1
ORDER BY b.backup_finish_date DESC;

Rows with empty media name and description have no recorded link to a snapshot identifier (our reading). For each row, confirm the named snapshot still exists in the storage system's own console, read-only, and that its retention is at least as long as the .bkm file's.

What does a .bkm file say?

RESTORE HEADERONLY FROM DISK = 'path\to\file.bkm' WITH METADATA_ONLY;
RESTORE LABELONLY FROM DISK = 'path\to\file.bkm';
RESTORE FILELISTONLY FROM DISK = 'path\to\file.bkm' WITH METADATA_ONLY;

These read the file and restore nothing. FILELISTONLY lists the data and log files the snapshot must contain.

These checks are partial. They cannot prove the storage snapshot is complete or consistent, and msdb history may be pruned (our reading).

Safe restore rehearsal

Run this only in an authorised, isolated test environment on storage that supports scripted snapshots, with a synthetic database (our recommendation).

  1. Create a test database with data and log files on separate test volumes; record row counts and a checksum query result.
  2. Suspend, snapshot, record: run the three steps with MEDIANAME set to the snapshot identifier. Time the suspended period.
  3. Insert more rows and take a streaming log backup.
  4. Restore on a second test instance: attach the snapshot volumes, restore from the .bkm with NORECOVERY and MOVE as needed, then apply the log with RECOVERY. Compare counts and checksum with the expected values.
  5. Negative test: try the restore without the snapshot attached and record the error, so the team recognises it.
  6. Controlled interrupted run: only under the reviewed lab failure-handling plan, suspend and thaw without a completed backup. Verify nothing remains suspended. Measure the subsequent differential scan duration and I/O: an invalid bitmap requires a whole-database scan, but the output backup size still depends on changed data and is not guaranteed to grow. Compare with a successfully completed snapshot and the separately configured copy-only case.

Diagnosing what you find

  • Database still suspended after a backup window: the script failed before the BACKUP step; the snapshot call hung; or a server-level suspend was used and a database-level thaw was attempted.
  • Restore cannot find data files: the snapshot volumes are not mounted; they are mounted at different paths and MOVE was not used; or the snapshot did not include every disk holding the files.
  • Restore reports an inconsistent database: the snapshot was taken outside the suspended window; only some disks were included; or a different snapshot from the one recorded was attached.
  • Differential scan I/O or duration increases: an interrupted snapshot/thaw can invalidate the bitmap and force a whole-database scan. Investigate actual changed data, the differential base and other backup jobs separately when output file size grows; a bitmap reset alone does not prove a larger backup file.

Have a qualified DBA and the storage administrator review the plan before any production use.

Worked example (illustrative)

The following is invented. A company backs up a 6 TB database with nightly snapshot backups and hourly log backups to a file share. Each .bkm file is kept for 35 days. A storage administrator later shortens snapshot retention to 7 days to recover space.

A restore to three weeks ago is requested. The .bkm file and log backups exist, msdb shows the full backup, but the snapshot named in MEDIANAME is gone. The point cannot be restored. The team aligns snapshot retention with metadata retention, adds a weekly streaming full backup to separate storage, and adds the read-only cross-check to its monthly review.

Risks and limits

  • A storage snapshot on the same array as the database shares that array's failures (our reading). Copy snapshots or take separate backups to other storage according to your recovery plan.
  • The suspend period pauses writes. Measure it in testing before using the feature on a busy system.
  • The snapshot step depends entirely on the storage vendor's documentation; check its consistency guarantees for multi-volume snapshots.
  • No command in this guide was run in a lab. Test every step in an isolated environment first.

Sources

All accessed 14 November 2026.

Related future topics

  • SQL Server system databases: backing up master, model and msdb alongside snapshot backups
  • Storage snapshot retention against database backup retention
  • Point-in-time restore from a snapshot full backup and streaming logs

This article was originally published on .