SQL Server 2005 has given us the option to create database snapshots to create a point-in-time image of the database. This gives us the option to restore from a point-in-time in case of user errors, like maybe accidentally truncating a table. MSDN has provided us a procedural approach on how to restore from database snapshots. But what I am more concerned the most is that I have already lost a lot of data from the time the snapshot was created to the point in time that the error occurred. And this is where your understanding of what happened and how you implement your disaster recovery plan come into the picture. As far as Microsoft is concerned, restoring a database from a snapshot overwrites the original source database.

But here’s what I’d do. I’d first backup the tail of the log before I restore from the database snapshot. After restoring from the snapshot, I’d restore my backups into another copy of the database and restore the tail of the log. This will be the state of the database after the accidental error occurred. Since I am concerned about the new records that were added after the accidental error, I would retrieve those from my database copy and insert those on my restored database. This will take a lot of work as you will be comparing the tables from the original and the copy database. At least you managed to restore the records faster than restoring from a FULL backup. There is no native way to solve this but there are a lot of third-party utilities that can just rollback a specific command from the transaction log. You can use the TableDiff utility to do this although it wasn’t meant to solve this type of problems