SQL Server Quickie #31 – Database Snapshots

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Today I have uploaded SQL Server Quickie #31 to YouTube. This time I’m talking about Database Snapshots in SQL Server.

Like or share to get the source code.

Thanks for your time,



  • Thomas Franz

    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_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’),


    – 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

  • Jeffrey Roughgarden

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

It`s your turn

Your email address will not be published. Required fields are marked *


Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top