XML FeedsLet me first start by saying that this is not a foolproof solution, it is just another way that could help you out when you by mistake delete some data. If someone deletes the data in the table just before you create the database snapshot then you will be out of luck.
The option in this article is also nice if your backups are terabyte size because your restore would take a while and even then all the tables would be affected. You would have to restore to another DB and then just pull down the data from the table you want.
Everyday we create a database snapshot on our mirrored instance at 9 AM. Just a little after 9 AM someone deleted some data from a table because the WHERE clause was incorrect. Since we had a database snapshot, we could easily pull the data back and it saved us some time since we didn't need to restore the database or recreate the data.
Now let's take a look at some code to see what can be done after a bad delete
First create a test database
- USE master
- GO
- CREATE DATABASE [test] ON PRIMARY
- ( NAME = N'test', FILENAME = N'C:\test.mdf' )
- LOG ON
- ( NAME = N'test_log', FILENAME = N'C:\test_log.LDF' )
- GO
Now create a table and populate it with some data
- USE test
- GO
- CREATE TABLE TestTable (id INT NOT NULL,somecol CHAR(100) DEFAULT 'a')
- GO
- INSERT TestTable(id)
- SELECT ROW_NUMBER() OVER (ORDER BY s1.id)
- FROM sysobjects s1
- CROSS JOIN sysobjects s2
Now it is time to create your snapshot
- USE master
- GO
- CREATE DATABASE TestSnapshot ON
- ( NAME = N'test', FILENAME = N'C:\testss.mdf' )
- AS SNAPSHOT OF Test;
- GO
If you run this, you will see that both the test database and the snapshot database table have the same number of rows
- SELECT COUNT(*) FROM TestSnapshot..TestTable
- SELECT COUNT(*) FROM Test..TestTable
A database snapshot is read only and you cannot delete data, try it out.
- USE TestSnapshot
- GO
- DELETE TestTable
Msg 3906, Level 16, State 1, Line 1
Failed to update database "TestSnapshot" because the database is read-only.
Now let's delete all the data from the table in the test database
- USE Test
- GO
- DELETE TestTable
Just to verify, grab the count from the table
- SELECT COUNT(*)
- FROM TestTable
So the table is empty, but you can still get all the data back by inserting into the table from the database snapshot
- INSERT TestTable
- SELECT *
- FROM TestSnapshot..TestTable
And now both tables have the same number of rows again
- SELECT COUNT(*)
- FROM TestSnapshot..TestTable
- SELECT COUNT(*)
- FROM Test..TestTable
Just a couple of things to consider. If you have a lot of activity in your database then the database snapshot might grow considerably, be aware of that or you will run out of space if you placed the database snapshot on a drive that doesn't have a lot of space.
If you data gets updated frequently then this won't work either, the table in question for us was one where we had end of day values stored.
To drop the snapshot, you just use a regular DROP DATABASE command
- DROP DATABASE TestSnapshot
To learn more about database snapshots, visit books on line: http://msdn.microsoft.com/en-us/library/ms175158.aspx
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.