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
chokeclose any connections alter database [SourceDatabase] set single_user with rollback immediate go -- restore from snapshot restore database [SourceDatabase] from database_snapshot = 'NewDatabaseName'
- 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: