Creating a Database Snapshot
Published: Jan 01, 2017
This is pretty simple really, but it’s useful code and therefore I wanted it on my website mainly for my reference on those days in which I can’t remember the syntax and want a simple copy and paste option. We all have our forgetful moments after all :o)

For those who aren’t sure, Database Snapshots are an Enterprise Edition only feature which are incredibly powerful when used correctly, but make sure you keep an eye on them so that they don’t get out of hand.

A Database Snapshot is a point in time view of your database.

At time of creation a Database Snapshot has virtually no size as it is simply a view of your database itself. However, what SQL Server does in order to maintain this point in time snapshot is that whenever you perform an alteration to your database (an insert, update, delete etc) then SQL Server will write the old data page to the snapshot before making the change to your database.

Therefore, if you were to take a snapshot and then change every record in your database your snapshot would become the same size as your database was when you took the snapshot.

As such, just make sure that if you’re using snapshots you keep an eye on how they grow.

However, that aside, they are VERY useful when you want to make changes and still have a point in time to refer to, or to roll back to, or to allow someone to run reports against point in time data.

So how do we create one?

use AdventureWorks2012

create database AdventureWorks2012_Snapshot on
(name = AdventureWorks2012_Data, filename = 'D:\SQLData\')
as snapshot of AdventureWorks2012

So what are we doing? Well we’re creating a snapshot against the AdventureWorks2012 database by simply specifying the logical name of the data file that we wish to snapshot. We don’t require a log, so we don’t need to provide one as a database snapshot is read only.

And when we’re done we can simply drop it as normal:

drop database AdventureWorks2012_Snapshot

And that’s all there is to it.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London



Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron