Rolling Back to a Database Snapshot
Published: 26th March 2015
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
go

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


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

use master
go

restore database AdventureWorks2012
from database_snapshot = 'AdventureWorks2012_Snapshot'
go


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.
Comments:
NB: Comments will only appear once they have been moderated.