--

Oops! Need an Undo in SQL? Try Snapshot

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.”

courtesy of photoxpress.com I have been testing out some interesting items that were mentioned in sessions at the PASS Summit in Seattle last month. One of these was the concept...

CONNECT WITH US

CATEGORIES

Global Knowledge is the worldwide leader in IT training and learning services. We empower organizations, teams and individuals with the skills and best practices necessary to leverage the technologies and competencies critical for sustained success. With over 15 corporate offices around the world, our 1,500 employees are helping clients succeed in over 100 countries. Learn more at www.globalknowledge.com.