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

<span class="kwrd">create</span> <span class="kwrd">database</span> [NewDatabaseName] <span class="kwrd">on</span> (
    name = [SourceDatabaseLogicalName],
    filename = <span class="str">'\file\to\create\snapshot.ss'</span>
) <span class="kwrd">as</span> snapshot <span class="kwrd">of</span> [SourceDatabase]

To Restore the Snapshot

<span class="rem">-- force <strike>choke</strike> close any connections</span>
<span class="kwrd">alter</span> <span class="kwrd">database</span> [SourceDatabase]
    <span class="kwrd">set</span> single_user
    <span class="kwrd">with</span> <span class="kwrd">rollback</span> <span class="kwrd">immediate</span>

<span class="kwrd">go</span>

<span class="rem">-- restore from snapshot</span>
<span class="kwrd">restore</span> <span class="kwrd">database</span> [SourceDatabase]
     <span class="kwrd">from</span> database_snapshot = <span class="str">'NewDatabaseName'</span>&nbsp;

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.