First let’s get things straight, this post is not an encouragement for anyone to spend more time with his/her Database or with Database Snapshots for that matter. If you’re married to your Database, that’s a different story all together and I don’t think I could help you. As database professionals we are always looking to improve the quality of our code and data.  This post is intended to show an easy way to use Database Snapshots in order to  test new code or change data and revert changes back quickly and easily.

What I want to accomplish in this post is show you two ways that Snapshots can help you when you are doing Testing and QA:

1. Easily undo data and object changes going back to how things were originally.
2. After running updates to data, comparing data before and after the updates.

Before I get to the good stuff, let me explain what Database Snapshots are and how they work exactly. Imagine that you are writing a letter to someone using some word processor application of your choice. You are satisfied with that draft letter and so you save it on your computer. You realize later that you may want to make some adjustments, but the software you use does not support some fancy change tracking features.  So what do you do? You probably copy that document somewhere else creating a Snapshot of it. You edit your draft document and save it but the next day you realize that you don’t like any of those changes you made and there is no way for you to remember all the changes you made to reverse them. The easiest thing to do is to just take a copy of your Snapshot and replace the working draft.

This example loosely resembles what happens with Database Snapshots. All the original data pages that have changed on your database are stored in a snapshot file. If you drop the snapshot, then the changes are kept in the database. Optionally, you can rollback to your snapshot and force your database to look like it was when the snapshot was taken. When I say, your database will look like it was when the snapshot was taken, that means all data and even objects. Basically what happens is those original pages that were stored in the snapshot replace the pages that were changed.  It’s almost like a huge UNDO button.

Microsoft has an excellent article if you would like more details on this feature: http://msdn.microsoft.com/en-us/library/ms175158.aspx

Snapshots also have another benefit in that you can have your users read from the snapshot to see how data looked when the snapshot was taken. This can be beneficial in many ways because you can compare data before and after changes have been made to help you test how your changes affected the data.

[sql]

--Create test database and some objects
CREATE DATABASE DBSnapshotTest
GO

USE DBSnapshotTest
GO

----Create Table
CREATE TABLE Test1 (
	column1 INT identity(1, 1)
	,column2 VARCHAR(10)
	,br1 INT
	)

--Insert some dummy data
DECLARE @counter INT = 0

WHILE @counter < 100
BEGIN
	INSERT INTO Test1 (
		Column2
		,br1
		)
	VALUES (
		'Hello' + cast(@counter AS VARCHAR(3))
		,1
		)

	SET @counter = @counter + 1
END

----Create Simple stored Procedure
CREATE PROCEDURE pr_testproc
AS
SELECT *
FROM Test1

--Creating initial DBSnapshot
--Make sure you verify your own paths
CREATE DATABASE DBSnapshotTest_Snapshot01 ON (
	NAME = N'DBSnapshotTest'
	,Filename = N'D:\DBSnapshots\DBSnapshotTest.ss'
	) AS Snapshot OF DBSnapshotTest

--File extension is not that important, I was taught to use .ss

/*Verify that you can Select from the Snapshot 
and the base table and that
both contain the SAME values */
SELECT *
FROM DBSnapshotTest_Snapshot01.dbo.test1

SELECT *
FROM DBSnapshotTest.dbo.Test1

--Updating Business Rule #1 values for column1 < 50
UPDATE Test1
SET br1 = 5
WHERE column1 < 50

--Updating Business Rule #1 values again for column1 >=50
UPDATE Test1
SET br1 = 10
WHERE column1 >= 50

/*Verify that you can Select from the Snapshot 
and the base table and that
both contain the DIFFERENT values
The Snapshot should contain the old values*/
SELECT *
FROM DBSnapshotTest_Snapshot01.dbo.test1

---The DB should contain the new values
SELECT *
FROM DBSnapshotTest.dbo.Test1

--Create a second snapshot
CREATE DATABASE DBSnapshotTest_Snapshot02 ON (
	NAME = N'DBSnapshotTest'
	,Filename = N'D:\DBSnapshots\DBSnapshotTest2.ss'
	) AS Snapshot OF DBSnapshotTest

---Alter a SP then revert after
ALTER PROCEDURE pr_testproc
AS
SELECT *
FROM Test1
WHERE column1 > 50

/*
Through SQL Server Management Studio (SSMS)
Click on Databases, under Database Snapshots
Expand Snapshot2
Expand Programmability
Right click on the stored procedure, Script Procedure As, 
Create To, New Query Window
Refer to the screen shot on this post
Now you can see that under the snapshot, 
the stored procedure DDL is the same as it was created originally
*/
[/sql]



[sql]
/*Drop Snapshot2 since we are happy with 
the stored procedure changes we made
We also need to do this, so that we can revert to Snapshot1
You cannot revert to a snapshot with another snapshot active
*/
DROP DATABASE DBSnapshotTest_Snapshot02

---Revert the DB to the snapshot1
---Snapshot1 was created Before the data was changed
USE MASTER

ALTER DATABASE DBSnapshotTest SET single_user WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DBSnapshotTest
FROM Database_SNAPSHOT = 'DBSnapshotTest_Snapshot01'

/*Verify that you can Select from the Snapshot 
and the base table and that
both contain the SAME values*/
SELECT *
FROM DBSnapshotTest_Snapshot01.dbo.test1

SELECT *
FROM DBSnapshotTest.dbo.Test1

USE DBSnapshotTest

--Update the business rules again
--Updating Business Rule #1 values for column1 < 50
UPDATE Test1
SET br1 = 5
WHERE column1 < 50

--Updating Business Rule #1 values again for column1 >=50
UPDATE Test1
SET br1 = 10
WHERE column1 >= 50

---The data between the snapshot and live DB should be different
--you can verify if you want
---Try Dropping the DB
USE master

DROP DATABASE DBSnapshotTest

--Cannot Drop the DB without Dropping Snapshot first
--Drop Snapshot1
DROP DATABASE DBSnapshotTest_Snapshot01

/*Now you cannot revert back to Snapshot1 which 
means that those data changes you made are now permanent
Of course, if you are using the full recovery 
model there are other options for recovery but they
are not as quick as using DB Snapshots
*/

--Verify your data updates are still there
SELECT *
FROM DBSnapshotTest.dbo.Test1

--Now you can drop the database once all snapshots have been dropped
USE master

DROP DATABASE DBSnapshotTest

[/sql]