Reverting a Failed Deployment With a Database Snapshot

A failed deployment is a bad moment to find out how you plan to undo it. A database snapshot gives you a quick way back to the state before the release. But it takes everything with it, including the good writes that came in after the snapshot. Decide how much you can lose before you start.

The plan you want at midnight

Picture a Friday night release. The scripts ran, the app is up, and then the support line lights up. A column is wrong, a procedure returns junk, and you want to go back. A restore from backup would take hours. A snapshot is the fast way back.

A database snapshot is a read-only view of the database as it was at one moment. SQL Server keeps only the pages that change afterward. It does not copy the whole database up front. That makes it cheap to create and quick to revert, and it makes it dangerous to confuse with a backup.

The demo creates two databases, SqlAuthorityDemo and its snapshot, and drops both at the end. The first block builds the source with one row. It also takes a full and a log backup to the NUL device, which writes nothing. That is fine for a demo and never fine for real backups.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo_Snap;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
ALTER DATABASE SqlAuthorityDemo SET RECOVERY FULL;
BACKUP DATABASE SqlAuthorityDemo TO DISK = N'NUL';

CREATE TABLE SqlAuthorityDemo.dbo.ReturnPoint (ItemId int PRIMARY KEY, ValueText varchar(20));
INSERT SqlAuthorityDemo.dbo.ReturnPoint VALUES (1, 'before');

BACKUP LOG SqlAuthorityDemo TO DISK = N'NUL';

Take the snapshot before the release

A snapshot needs a sparse file for each data file in the source. CREATE DATABASE wants a literal path, so the script builds the statement from the instance’s default data folder. The first result links the snapshot to its source. The second shows how little room the snapshot takes right now.

DECLARE @File nvarchar(4000) =
    CONVERT(nvarchar(4000), SERVERPROPERTY('InstanceDefaultDataPath')) + N'SqlAuthorityDemo_Snap.ss';
DECLARE @Sql nvarchar(max) =
    N'CREATE DATABASE SqlAuthorityDemo_Snap ON (NAME = SqlAuthorityDemo, FILENAME = N'''
    + REPLACE(@File, N'''', N'''''') + N''') AS SNAPSHOT OF SqlAuthorityDemo;';
EXEC (@Sql);

SELECT s.name AS snapshot_name, src.name AS source_name
FROM sys.databases AS s
JOIN sys.databases AS src ON src.database_id = s.source_database_id
WHERE s.name = N'SqlAuthorityDemo_Snap';

SELECT size_on_disk_bytes / 1024 AS snapshot_kb
FROM sys.dm_io_virtual_file_stats(DB_ID(N'SqlAuthorityDemo_Snap'), NULL);

The first result shows SqlAuthorityDemo_Snap as the snapshot of SqlAuthorityDemo. The snapshot is only 128 KB on disk in this run, a fraction of the source file. Sizes differ per server, so trust the shape, not the number.

Let the release go wrong

Now play the release. The first change stands in for the bad deployment: row 1 changes from before to after. The second is a good write that arrived while you were busy: a new row 2. Then we read both the source and the snapshot.

UPDATE SqlAuthorityDemo.dbo.ReturnPoint SET ValueText = 'after' WHERE ItemId = 1;
INSERT SqlAuthorityDemo.dbo.ReturnPoint VALUES (2, 'later');

SELECT 'source' AS phase, ItemId, ValueText FROM SqlAuthorityDemo.dbo.ReturnPoint
UNION ALL
SELECT 'snapshot', ItemId, ValueText FROM SqlAuthorityDemo_Snap.dbo.ReturnPoint
ORDER BY phase, ItemId;

SELECT size_on_disk_bytes / 1024 AS snapshot_kb
FROM sys.dm_io_virtual_file_stats(DB_ID(N'SqlAuthorityDemo_Snap'), NULL);

The snapshot still shows row 1 as before and has no row 2. The source shows after and later. The snapshot also grew, from 128 KB to 256 KB in my run, because it saved the pages the changes touched. The more the database changes, the bigger it gets, so watch the disk.

Revert, and see what you lose

The revert needs exclusive access to the source, so the script kicks everyone out first. It then restores from the snapshot and opens the database again. Notice the cost in the result.

ALTER DATABASE SqlAuthorityDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE SqlAuthorityDemo FROM DATABASE_SNAPSHOT = 'SqlAuthorityDemo_Snap';
ALTER DATABASE SqlAuthorityDemo SET MULTI_USER;
GO
SELECT 'reverted' AS phase, ItemId, ValueText
FROM SqlAuthorityDemo.dbo.ReturnPoint
ORDER BY ItemId;

Row 1 is back to before. Row 2 is gone. That is the real price. The revert is for the whole database, so the bad change and the good late write disappear together. If your release window allows writes, you need a plan for them. Maybe the app is in read-only mode during the release. Maybe you export the new rows first.

Your log backups need a new start

A revert rebuilds the log. That breaks the chain of log backups. The next query asks SQL Server for the last log backup it knows about, and then tries a log backup.

SELECT DB_NAME(database_id) AS database_name, last_log_backup_lsn
FROM sys.database_recovery_status
WHERE database_id = DB_ID(N'SqlAuthorityDemo');

BEGIN TRY
    BACKUP LOG SqlAuthorityDemo TO DISK = N'NUL';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message;
END CATCH;

The last log backup value is NULL, and the log backup fails with error 3013. SQL Server has forgotten the old chain. A new full backup restarts it. After that, a log backup works again. The final block shows that, then removes both databases and the backup history rows for the demo.

BACKUP DATABASE SqlAuthorityDemo TO DISK = N'NUL';
BACKUP LOG SqlAuthorityDemo TO DISK = N'NUL';
SELECT 'log backup works again' AS result;
GO
USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo_Snap;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'SqlAuthorityDemo';

One more caution. A snapshot depends on its source. If the source storage dies, the snapshot dies with it, so keep your normal tested backups. After any revert, check the application and its permissions.

Take a snapshot before your next release, and decide in advance what you can afford to lose.

A database snapshot is not a backup, it is a return point that depends on its source.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.