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