SQL Snapshot Notes

Sometimes when testing you need to repetitively restore a database back to a certain point in time. For very large databases the regular SQL restore can take quite a while. It seemed to me that using snapshots instead of regular backups cut this restore down quite a bit.

To Create the Snapshot

create database [NewDatabaseName] on (
name = [SourceDatabaseLogicalName],
filename = '\file\to\create\snapshot.ss'
) as snapshot of [SourceDatabase]

To Restore the Snapshot

-- force choke close any connections
alter database [SourceDatabase]
set single_user
with rollback immediate

go

-- restore from snapshot
restore database [SourceDatabase]
from database_snapshot = 'NewDatabaseName' 

Notes

• NewDatabaseName: The name of a database that doesn’t exist yet, but will be created and store the snapshot information.
• SourceDatabase: The name of the database you want to create a snapshot of.
• SourceDatabaseLogicalName: The “logical name” of the main MDF file for the database you want to create a snapshot of. In SQL Management Studio, do right-click/Properties for the database, and look here:

This entry was posted in CodeMinder, SQL. Bookmark the permalink.