DB-유

SQL Server Database Snapshots for Quick Rollback

· mssql, database snapshot, deployment, rollback

Before a risky deployment on a big database, I used to take a full backup and just accept the wait: an hour for the backup, and another hour or two for a restore I hoped never to run. A database snapshot is the same safety net in a few seconds, and reverting takes minutes. They were Enterprise-only for years, but since SQL Server 2016 SP1 they work in Standard too, and now I use them before nearly every release.

How a snapshot works

A snapshot is a read-only, point-in-time view of a database, implemented as a sparse file per data file. It starts empty. When a page in the source database is about to be modified for the first time after the snapshot, the original page is copied into the sparse file (copy-on-write). Reads from the snapshot come from the sparse file if the page was changed, otherwise from the source. So a snapshot costs almost nothing to create and grows only with the amount of change in the source.

Two consequences follow: the snapshot lives on the same instance and depends on the source, so it is not a backup; and it must be on a volume that supports sparse files (NTFS, not ReFS on older Windows versions).

Get the file list

The snapshot needs one sparse file per data file of the source. Log files are not included.

SELECT name, physical_name, type_desc FROM Sales.sys.database_files WHERE type_desc = 'ROWS';

Create it before the change

CREATE DATABASE Sales_Snap_PreDeploy
ON
    (NAME = N'Sales', FILENAME = N'S:\Snapshots\Sales_PreDeploy.ss'),
    (NAME = N'Sales_Archive', FILENAME = N'S:\Snapshots\Sales_Archive_PreDeploy.ss')
AS SNAPSHOT OF Sales;

The NAME values must match the logical names of the source's data files. The .ss extension is convention only. The statement completes in a second or two regardless of database size.

Verify how much space it actually occupies versus what Explorer shows:

SELECT DB_NAME(database_id) AS snapshot_db, file_id,
       size_on_disk_bytes / 1024 / 1024 AS used_mb
FROM sys.dm_io_virtual_file_stats(DB_ID('Sales_Snap_PreDeploy'), NULL);

Explorer reports the full logical size of the sparse file; size_on_disk_bytes is the real consumption.

Deploy, and compare if you like

Run the release script against Sales. The snapshot is queryable throughout, which makes it handy for before/after checks without touching a backup:

SELECT COUNT(*) FROM Sales.dbo.Orders;
SELECT COUNT(*) FROM Sales_Snap_PreDeploy.dbo.Orders;

SELECT o.OrderID, o.Status AS new_status, s.Status AS old_status
FROM Sales.dbo.Orders o
JOIN Sales_Snap_PreDeploy.dbo.Orders s ON s.OrderID = o.OrderID
WHERE o.Status <> s.Status;

Because the snapshot is read-only, nobody can accidentally write to it.

Reverting after a bad deployment

Reverting overwrites the source database with the pages stored in the snapshot, bringing it back to the exact moment the snapshot was created. Requirements: only one snapshot of the source may exist, no other connections may be active, and the source must not have read-only or offline filegroups.

USE master;
-- drop any other snapshots of Sales first
ALTER DATABASE Sales SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE Sales FROM DATABASE_SNAPSHOT = 'Sales_Snap_PreDeploy';
ALTER DATABASE Sales SET MULTI_USER;

The revert takes roughly as long as it takes to copy the changed pages back, usually minutes. Afterwards the transaction log is rebuilt, so the log backup chain is broken: take a full backup immediately.

BACKUP DATABASE Sales TO DISK = N'B:\SQLBackup\Sales\Sales_after_revert.bak' WITH COMPRESSION, CHECKSUM, INIT;

Drop it once the change is accepted

Don't leave snapshots lying around; I've forgotten one before and only noticed when the disk alert fired. Every write to the source pays the copy-on-write cost while a snapshot exists, and the sparse file keeps growing.

DROP DATABASE Sales_Snap_PreDeploy;

What snapshots exist right now?

SELECT name, source_database_id, create_date, state_desc
FROM sys.databases WHERE source_database_id IS NOT NULL;

Shows all snapshots on the instance and which database each belongs to. After a revert, run DBCC CHECKDB ('Sales') WITH NO_INFOMSGS once; it is not required but it is cheap reassurance.

Notes and limitations

  • "Msg 3137: Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files." Exactly what it says: drop every other snapshot of the same source before RESTORE ... FROM DATABASE_SNAPSHOT.
  • "Msg 5119: Cannot make the file 'S:\Snapshots...' a sparse file. Make sure the file system supports sparse files." The volume is FAT32, a compressed folder, or ReFS on a Windows version without sparse file support. Use a plain NTFS volume.
  • Snapshots are not a substitute for backups. If the source database becomes corrupt or the disk dies, the snapshot dies with it. Keep the regular backup schedule running.
  • Full-text catalogs are not included in a snapshot, and a revert drops them. FILESTREAM data is not supported at all: the snapshot works but the FILESTREAM filegroup is offline within it.
  • The source database cannot be dropped, detached or restored while any snapshot exists. DROP DATABASE Sales fails with "Cannot drop database 'Sales' because it is currently in use"-style messages until the snapshots are gone.
  • Writes to the source are slower while a snapshot exists, noticeably so on spinning disks under heavy update load. For a deployment window this is irrelevant; for a snapshot left in place for a week it is not.

Snapshot, deploy, check, drop. Five minutes, and it has saved me a full restore more than once.