In SQL Server 2005 MSFT introduced very new feature … Database Snapshots … which is basically a read-only view of database at the time when snapshot was taken. Just as a side note…even at the time this feature was introduced in SQL Server, other age old DBMSes like Oracle and DB2 were already offering similar feature in their systems. Database Snapshots can prove really useful if used wisely or can bite you back if used without any thoughts … hence … The Good, bad and ugly … 
Another note before starting … unfortunately Database Snapshots are kind of toys for rich only 
… which means you have to have Enterprise edition to use this feature. But since I have developer edition … I am more like a lottery winner 
… because all Enterprise features are available in Developer edition … with that being said … let’s prepare staging environment.
I have a test database with few sample tables that I have derived from AdventureWorks tables. I created this database into a temp HDD created using one of my favorite RamDisk software. Which gives me ability to blow up that drive without any issue at any time (which exactly what I will do at the end of this demo in the Ugly part ). But say if you don’t have enough RAM you can use USB thumb drive as a place to restore database backup. I restore database with following command …
Once that is done, I created a snapshot of database at location other than that volatile drive (again for to demo the Ugly part ). Following is a simple script that I used to create database snapshot
With those things are in place … let’s begin …
The Good …
As I mentioned before snapshots are read only copy of database at a time when snapshot was taken. But another good advantage is they give you ability to restore your database back to the time when snapshot taken almost instantly. This can particularly be handy when you want to try “something“ quickly and revert it back if something goes south ways.
--- THE GOOD -- Use of Snapshot option --- -- Create Snapshot of database CREATE DATABASE TestDB_V_Snap ON ( NAME = TestDB, FILENAME = 'D:\Temp2\DBSnapshots\TestDB_V.ss' ) AS SNAPSHOT OF TestDB_V; GO -- Selete sample data USE TestDB_V SELECT * FROM TableBar TB WHERE TB.StateProvinceCode='CA' GO -- Delete few records USE TestDB_V DELETE TB FROM TableBar TB WHERE TB.StateProvinceCode='CA' GO -- Double check deletion USE TestDB_V SELECT * FROM TableBar TB WHERE TB.StateProvinceCode='CA' GO -- Restore Snapshot USE master RESTORE DATABASE TestDB_V from DATABASE_SNAPSHOT = 'TestDB_V_Snap'; GO -- Check revert operation USE TestDB_V SELECT * FROM TableBar TB WHERE TB.StateProvinceCode='CA' GO
In database above, I have a table called TableBar. Which has few thousand records stored in it and there is a column which indicates states. Now I deleted all the records which contains state= ‘CA’. But then I realized that I should have deleted records with state=’WA’ not ‘CA’. Good thing is I have database snapshot, I can just revert it back to the time when I took the snapshot, which is just before I deleted records. I just run command below, and voila … deleted records are back !!
So the good thing about snapshots is, you can revert back your database into transactional consistent state using snapshot.
The Bad …
But as you know there is nothing like all good and no side effects in life … apparently it stay true with databases as well. This all good database snapshot feature is also having some side effects.
If you look at size of snapshot file that we created in the beginning it has size of very few KBs, it is because snapshots doesn’t include all database data but it includes only database changes done since snapshot was taken. In other words it means that with time passing and more data modification being done the size of snapshot will increase and it could grow very large with time.
Another bad thing about snapshot is, it makes database perform slower because while you make any change to database the system also requires to update snapshot to keep it “in sync” just so user can restore database back in time when snapshot was taken.
Actually above two are my favorite BADs .. but on MSDN you can find number of limitations of Snapshots.
The Ugly …
Most scary part of Snapshots is it gives you false sense of recoverability of database if something goes wrong … but fact it snapshots are not as reliable as they seem to be. And you will be in big trouble if you solely rely on database snapshot to save your butt if need arises. That’s in next example.
--- THE UGLY -- Use of Snapshot option --- -- Delete few records USE TestDB_V DELETE TB FROM TableBar TB WHERE TB.StateProvinceCode='CA' GO -- Double check deletion USE TestDB_V SELECT * FROM TableBar TB WHERE TB.StateProvinceCode='CA' GO
Say you loose a drive which is holding your database (I simulated this scenario by deleting that RAMDrive, if USB thumb drive is used then just yank it out of USB socket). And then I tried to restore snapshot using same command as above and it will shamelessly fail with error
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Msg 823, Level 24, State 3, Line 2
The operating system returned error 21(The device is not ready.) to SQL Server during a write at offset 0x00000000498000 in file ‘G:\DBFiles\TestDB.mdf’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
In simple words, there is nothing in this world that can replace good ol’ database backup (may be I am getting little carried away … but it is the truth)
So .. what’s final verdict ? To use or not to use ? I think answer is “it depends”. Because even though database snapshots are not reliable means to recover database but still they can be really handy when you want to try something quickly and have something that lets you revert back your changes if something doesn’t work out.
That’s it for now …
It’s Just A Thought … 
