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.

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

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.