I am working quite a lot with virtual machines in Hyper-V and VMWare. In these products you have a really useful feature called “snapshot”.

The snapshot feature is most useful when you want to preserve the state of the database so you can return to the same state repeatedly. For example if you are doing a scary update or something you have the possibility to “roll back” these changes.

Did you know that you could make snapshots even in SQL Server?

Lets start by creating a snapshot of the AdventureWorksDW2012 database and create a “super important” table.

-- change database
use [AdventureWorksDW2012]

-- Create a super important table
create table mySuperImportantTable ([ID] int, [Desc] varchar(2000))

-- Insert some values
insert into mySuperImportantTable values (1, 'Secret 1')
insert into mySuperImportantTable values (2, 'Secret 2')
insert into mySuperImportantTable values (3, 'Secret 3')
insert into mySuperImportantTable values (4, 'Secret 4')
insert into mySuperImportantTable values (5, 'Secret 5')

-- Create a snapshot of the database
CREATE DATABASE AdventureWorksDW2012_Mysnapshot ON
(
NAME = AdventureWorksDW2012_Data,
FILENAME = 'C:\db snapshotfile\AdventureWorksDW2012_Mysnapshot.ss' )
AS SNAPSHOT OF [AdventureWorksDW2012];
GO

Then the big mistake happens. We delete the table by accident.

-- Oh no I did a mistake
drop table mySuperImportantTable

Because snapshot files tend to get big I am really curious about the size of it.

Ouch… It looks really big 😦 But lets look at the actual disk usage

Ahh… only 960 kB. I can live with that.

Okey back to my snapshot. I can write some basic SQL to list all my snapshots.

select * from sys.databases where source_database_id is not null

Lets try to rescue the day by restoring from my snapshot and then get the data from the database

-- I want to restore my database from snapshot
USE MASTER
RESTORE DATABASE AdventureWorksDW2012
FROM DATABASE_SNAPSHOT = 'AdventureWorksDW2012_Mysnapshot'

-- Is my table there?
use AdventureWorksDW2012
select * from mySuperImportantTable

Cool. My snapshots saved my day. Then some cleaning. Delete the snapshot and then my sample table.

 -- Drop my snapshot database
DROP DATABASE AdventureWorksDW2012_Mysnapshot ;

-- Drop my table
drop table mySuperImportantTable