Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

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\AdventureWorks2012_Data.ss')
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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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


© Copyright 2020 SQLTraining Ltd.