T-SQL Tuesday #85 Alternatives To Backup & Restore

For T-SQL Tuesday #85, Kenneth Fisher (b|t) says "let’s hear it for backup and recovery!". I'm sure if you follow along with the others who participate, you'll see plenty of articles that reinforce the importance of database backups. I'll be right there with them in agreement. But I want to take this in a different direction. This may sound like heresy, but restoring a backup can be a bad idea. I'll explain why, and present some alternatives.

Overwhelmingly, the most common backup request I get goes something like this: "Hey Dave. I'm about to update some data. Can you take a full backup before I run my script?" Well, I *could*, but I probably won't. If I can't perform a point-in-time restore without advanced notice, I'm not a very good DBA, am I? Even if I'm unprepared and am given advanced notice, I'll opt for a transaction log backup or a differential backup, depending on the recovery mode of the database. (A full backup would be an unnecessary waste of time and resources.) But really, a backup of any type is not the best choice here. If the requester comes back to you 10 minutes later and asks you for a restore to "undo" the changes, you will have lost 10 min (or more) of other data changes. Not good. For simple data changes, there are better options than backup/restore.

Explicit Transactions

It's a little unsettling how frequently the use of transactions is overlooked. An explicit transaction is probably the easiest way to "undo" your DML changes. They are great for situations where you can verify your changes directly in SSMS. This is likely not an option if verification must be done outside of SSMS (eg. via an application).

Audit Tables

One or more audit tables can be used to track changes to a source table, providing a way to "undo" your work. This involves more T-SQL/scripting work than a database restore (or rolling back a transaction). But you have a lot of flexibility. Here are a few options:

• Triggers: although this may be a bit of overkill, you could use triggers (for INSERT, UPDATE, and/or DELETE) to track changes to a source table in one or more audit tables.
• Manual Approach: you could duplicate the inserts on source tables to audit tables. You would also have to copy original source table rows that are updated or deleted to audit tables.
• OUTPUT Clause: you could use the OUTPUT clause, which returns information from each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be inserted into an audit table.

Database Snapshots

A snapshot creates a read-only static view of a source database. With a snapshot, DML statements can be run on the source database and the snapshot database will preserve the original data. The snapshot can be used to "undo" data changes in the source database. There'd likely be more T-SQL/scripting work involved than a simple database restore. However, a snapshot has less "overhead" than a backup (at first). As noted in the MSDN documentation, "As the source database is updated, the database snapshot is updated. Therefore, the longer a database snapshot exists, the more likely it is to use up its available disk space." In addition, there are prerequisites for and limitations on database snapshots.

Temporal Tables

This is a new feature for SQL Server 2016. If you've ever used triggers to maintain audit trails of updates and deletes, you may find this feature familiar. MSDN tells us "When the data in temporal table is modified, the system builds version history transparently...[and] also provides convenient and easy ways to get insights from data history through extended Transact-SQL syntax." Sounds pretty cool, right? SQL keeps a history of your DML so you can go back in time and retrieve the old data when you need to fix an "oops". Here's a couple more links that may help you get started: