SQL Server 2008 Database Snapshots

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

Comments

• Anonymous
  January 01, 2003
  Here are some notes on “SQL Server 2008 Database Mirroring” I took while attending an advanced class