SQL Server 2008 Database Snapshots

  • Article

Here are some notes on “SQL Server 2008 Database Snapshots” I took while attending an advanced class on SQL Server taught by Greg Low (from https://sqlblog.com/blogs/greg_low/ and https://www.sqldownunder.com/).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

Database Snapshots

  • Read-only, static views of an entire database
  • See https://msdn.microsoft.com/en-us/library/ms175158.aspx
  • Used for: Reporting, protection from user error, major updates, unit testing
  • Careful – When using with major updates or bulk operations, consider the impact
  • Uses NTFS sparse files, always in the same server as database
  • CREATE DATABASE … ON (FILE=Name, FILENAME='...') AS SNAPSHOT OF …
  • See https://msdn.microsoft.com/en-us/library/ms175876.aspx
  • File specified under FILENAME contains changes in the original database since snapshot time
  • Can’t drop, detach or restore database (or add more files to it) when a snapshot exists
  • Careful - If you lose the original database files, you lose the snapshot 

Considerations 

  • Can create multiple snapshots of the same database
  • Careful – Additional write workload when updating original database with multiple snapshots
  • Can’t use with master, model or tempdb
  • Can’t change permissions on snapshot after it is created
  • No option to refresh a snapshot. Need to drop and recreate. 
  • If creating on mirrored database, the mirror needs to be synchronized
  • Created always with ALLOW_SNAPSHOT_ISOLATION set to ON

Demo

  • Create a database, create a table, insert a few rows
  • Create snapshot with CREATE DATABASE … AS SNAPSHOT OF …
  • Query the table in the snapshot
  • In Windows Explorer, at "Size" and "Size on Disk" properties of the file
  • Go back to original database, insert a few more rows, update some rows, delete some rows, query the table
  • Query the table in the snapshot - verify it has the old state
  • In Windows Explorer, at "Size" and "Size on Disk" properties of the file again