Rolling Back to a Database Snapshot
Published: Jan 08, 2017
One of the best features of a database snapshot is the ability to roll back your changes and revert to the snapshot.

Therefore what you can do is take a snapshot, make a load of changes (whether testing new code, trying out something new etc) and then if it all goes pear shaped you can simply revert back to the snapshot and all will be well.

Obviously you should only really be doing this in test environments when playing with new things, but it never hurts to do this in production during a release to give you a roll back option if you need one.

So how do we do this?

Well let’s first create a snapshot (as per my last post):

use AdventureWorks2012

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

So let’s say we’ve made some changes that we want to roll back… so how do we revert?

use master

restore database AdventureWorks2012
from database_snapshot = 'AdventureWorks2012_Snapshot'

Now all your changes will be rolled back to the point in time at which you took the snapshot… and WAY faster than you could ever do with a database restore.

Don’t forget to clean up afterwards:

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