Fast Restores with Sql Server Snapshots

Using Backup/Restore to reset you data is ok but it can take a while. Snapshots are a much faster alternative. On my system a 20Gb snapshot restores in less than 10 seconds.

What are snapshots
A database snapshot is a readonly view of a database at the time the snapshot was created. The exist as a database on the server – you can find them in ‘Database Snapshots’ in the Sql Managment Studio object explorer. You can use them for reporting but here we are creating them, then restoring them over the main database.

Creating snapshots
Here’s the script to create a snapshot. It doesn’t need much explanation…

If db_id('DemoSnapshot') is Not Null
Begin
    Drop Database DemoSnapshot
End


CREATE DATABASE DemoSnapshot ON ( NAME = DemoDatabase, FILENAME =  'E:\TempBackups\Demo.ss') As Snapshot of DemoDatabase

If your database filename doesn’t match the database name you might get an error that says all files must be specified. In that case you need to pass the file name to the Name parameter.

Restoring snapshots
The script to restore the snapshot is simple as well. It closes existing connections by putting the database into single user mode, restores the snapshot, then puts the database back into multi user mode.

ALTER DATABASE DemoData SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE DemoData From Database_Snapshot = 'DemoSnapshot'
GO

ALTER DATABASE DemoData SET MULTI_USER
GO

That’s it, run the restore script whenever you want to put the database back to the saved state.