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:

image

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