
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]
Doron Farber
August 6, 2013 at 12:41 PM
Interesting article and very detailed one. Regards, Doron
Ayman El-Ghazali
August 6, 2013 at 12:44 PM
Thanks for your comment.
Dave Dustin
August 9, 2013 at 3:00 AM
There is a significant “bug” associated with reverting a database from a snapshot. The transaction log size is set to 0.5MB, meaning you have to reset it back to normal afterwards. See Paul Randal’s blog for more info http://www.sqlskills.com/blogs/paul/bug-reverting-from-a-database-snapshot-shrinks-the-transaction-log-to-0-5mb/
Ayman El-Ghazali
August 9, 2013 at 8:58 AM
Very interesting find! Thanks for sharing this with us. I’m glad I’ve only used this in staging for QA purposes so far because it would cause problems in production.