2 thoughts on “SQL Server Quickie #31 – Database Snapshots”

1. 

   Thomas Franz

   01/04/2016 at 12:40 PM

   some points to mention:
   – if you modify a page multiple times, the original page will be stored only once in the snapshot (so if you update ‘B’ to ‘BB’, then to ‘BBB’ then to ‘BBBB’, your snapshot will still only have on (the original ‘B’-version of this page)

   – you can create multiple snapshots for one database at the same time (e.g. when deploying multiple complex changes to your test db)
   – every snapshot slows the writes to your DB (so having multiple snapshots on your production database is no good idea)
   – you can restore to any of this snapshots
   – but if you restore to a snapshot, you have to drop all other snapshots because they become invalid
   – Reverting breaks the log backup chain -> you have to take a FULL backup after the restore (exept the DB is in the SIMPLE recovery mode)

   The syntax to create a snapshot, when you have multiple files in your DB is:
   CREATE DATABASE mydb_snap ON
   (NAME =mydb, FILENAME=’e:\MSSQL12.MSSQLSERVER\MSSQL\DATA\mydb.ss’),
   (NAME =mydb_file_2, FILENAME=’e:\MSSQL12.MSSQLSERVER\MSSQL\DATA\mydb_file_2.ss’),
   (NAME =mydb_file_3, FILENAME=’e:\MSSQL12.MSSQLSERVER\MSSQL\DATA\mydb_file_3.ss’),
   …
   AS SNAPSHOT OF mydb

   – if you want, you can create the snapshots for different files on different drives / folder (e.g. on the original drive)
   – Reverting drops all full-text catalogs – be aware of this if you are using any
   – be aware, that snapshots can grow very large if you do write-heavy stuff as rebuilding indexes

   Reply
2. 

   Jeffrey Roughgarden

   01/04/2016 at 7:01 PM

   Klaus and Thomas, thank you both for your succinct presentation and comment.

   Reply