Resetting a Test Database With Database Snapshots

Repeatable tests need the same starting data each time. Resetting a test database with a database snapshot can return a SQL Server database to a chosen point quickly when the source and its snapshot remain healthy.

Know What a Snapshot Captures Before Resetting a Test Database

A database snapshot is a read only, transactionally consistent view of a source database at creation time. SQL Server uses sparse files to preserve original pages as source pages change. The snapshot initially needs relatively little space, then grows according to changed pages. This makes it practical for resetting a test database to a short lived baseline where repeated runs modify the same database.

I use a snapshot only after the test database has the schema, reference data, and settings the run should begin with. If the baseline is already wrong, revert will reproduce the mistake faithfully. Record the creation point and the expected data checks. A snapshot is attached to its source database and storage, so it is a local reset mechanism rather than a portable archive.

Check Eligibility and Disk Space

Confirm the SQL Server edition and current feature support, the source database state, and any feature restrictions before planning a snapshot based workflow. Snapshot creation requires a sparse file for each source data file, using the logical name of that source file. The transaction log is not included as a snapshot file. Use a dedicated, monitored location on suitable Windows storage.

A sparse file can grow substantially as the source changes. Plan for the amount of data the test modifies, not the tiny initial file size. If the sparse file fills its volume, the snapshot can become unusable. I keep the test run short and monitor free space so a supposed reset point does not vanish halfway through a long experiment.

Find the Logical Data File Names

The CREATE DATABASE statement needs the logical name of every data file in the source database. This read only query shows the names and physical locations. The example assumes a test database named TestLab. Change it to the actual database before executing anything. Review the returned rows and build one ON entry per data file.

Do not copy a sample file name into production without inspection. A database with multiple data files needs a matching sparse file for each one. Keep snapshot files on a volume where the SQL Server service account has the required access and where growth can be monitored. The path in the next example is illustrative.

SELECT name, type_desc, physical_name,
       size * 8.0 / 1024 AS size_mb
FROM sys.master_files
WHERE database_id = DB_ID(N'TestLab')
ORDER BY file_id;

Create the Baseline Snapshot for Resetting a Test Database

Take the snapshot immediately before the test sequence, after other writers are stopped and the baseline checks pass. The example assumes the source has one data file whose logical name is TestLab_Data. Substitute every actual logical data file returned by the inventory query. Keep snapshot names descriptive so an operator can tell which run they belong to.

SQL Server creates the snapshot as a separate database object. You can query it to inspect the captured state, but it is read only. Avoid long lived snapshots on busy databases because copy-on-write work and sparse file growth add I/O and operational exposure. The best reset point is close to the disposable work it protects.

CREATE DATABASE TestLab_before_run
ON
(
    NAME = TestLab_Data,
    FILENAME = 'D:\SQLSnapshots\TestLab_before_run.ss'
)
AS SNAPSHOT OF TestLab;

Revert Only After the Test Is Done

Reverting overwrites the source database’s changes since the snapshot was created. Stop test clients, confirm the snapshot is the intended baseline, and ensure no other snapshot blocks revert. SQL Server requires the source database to be eligible for revert and the operation needs exclusive access. Treat it as a destructive reset of a disposable database, not a casual undo button on a shared environment.

The command is short, which is why the preflight matters. After it completes, reconnect the test client and verify a few baseline values before the next run. If a test also changed objects outside the source database, such as server logins or external files, reverting the database cannot restore those. The full test fixture needs its own reset plan.

RESTORE DATABASE TestLab
FROM DATABASE_SNAPSHOT = 'TestLab_before_run';

Understand the Sparse File Tradeoff

A snapshot preserves old page versions when source pages are first modified after snapshot creation. A workload that touches most of the database can make the sparse file large, even if each row changes only a little. Repeated updates to the same pages have a different growth pattern from updates spread across the entire database. Estimate using a realistic trial and observe actual volume usage.

Dropping a snapshot removes that reset point. Before cleanup, confirm no pending test needs it and no reporting query depends on it. Make snapshot naming and cleanup part of the test harness, with a visible record of the baseline and run identifier. Otherwise yesterday’s snapshot can look like today’s and quietly reset the wrong state.

Keep a Real Backup Strategy Beyond Resetting a Test Database

A database snapshot is not a backup. It depends on the source database and its storage. You cannot back it up as a snapshot. A disk failure that takes the source or the sparse files takes the snapshot too. Revert also returns the whole database to the snapshot point, so it is unsuitable when you need to recover only selected changes from a shared test environment.

Ask what would happen if the test server disappeared entirely. A backup stored in an independent failure domain can support recovery on another server. A snapshot on the lost server cannot. Use backups for protection and snapshots for fast, local, repeatable resetting of a test database. Test both workflows separately so a convenient reset does not become the only recovery story.

Which changes live outside the database snapshot? A test can write files, send messages, or update another database. Reverting TestLab will not undo those effects. I isolate such dependencies or give the harness a separate cleanup step. Check the baseline after revert and before another run so a partial reset is discovered immediately, not after a confusing test failure.

Related reading on this blog: Recovering from Snapshots: Notes from the Field #078 and 2008: Introduction to Snapshot Database: Restore From Snapshot.

A database snapshot is not a backup, it is a local reset point with dependencies.

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