Oops! Need an Undo in SQL? Try Snapshot

Image courtesy of multimedia-stock.com

A great feature in SQL Server 2005 and above is the Snapshot database. It appears to be a complete read-only copy of a database. All data tables, views, stored procedures, users, permissions, etc. It’s just read-only. But under the hood, it’s a lot more efficient than that. This feature gives us the opportunity to undo accidental updates and deletes without inconveniencing anyone. It complements the classic Backup and Restore operations but does not replace them. It’s a different technology. Sparse database technology to be exact.

When you create a Snapshot, SQL Server creates a sparse database which effectively is an empty shell to begin with. That’s why it’s so quick to create. It appears to be the same allocated size as the associated data file but if you look at the “Size on disk” property you will see a different picture. 128 KB in my tests. When the source database is updated, it performs a copy-on-write operation which copies the original data page to the Snapshot database before the update. This is done only when a data page changes first time. The Snapshot database is maintained internally.

Once created, we can undo certain accidental operations. When that DBA drops a table by accident, or deletes thousands of rows, or applies an update without a WHERE clause, the Snapshot can help. All are “Resume generating events” but with a Snapshot database available you can play the hero. Since the data object is unchanged in the Snapshot, it can be quickly copied back via T-SQL without disconnecting the users or affecting other data. A common strategy is to drop and create a Snapshot every day at a particular time to assist with undo requirements. Or you can use a Snapshot to create a read-only copy of a Mirror database to allow reports to run against it.

Of course, there is some overhead to maintain the Snapshot and this has to be assessed to be acceptable. Also, a Snapshot will not help you in cases of corruption. If the source database is corrupt then so is the Snapshot so classic Backup and Restore is still needed to save the day in those cases.

But now if someone asks you if you can restore an individual table to SQL Server, the answer is… “Yes.”

Related Courses

SQL Server 2008 for Administration (M6231, M6232)

SQL Server 2005 Administration (M2780)

In this article

Join the Conversation