SQL Server Quickie #31 – Database Snapshots

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,

-Klaus

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

  1. 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, 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

Leave a Comment

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

Enjoy one of my newest Online Trainings:

Design, Deploy, and Optimize SQL Server on VMware

EUR 699 incl. 20% VAT