Alternatives to SQL Backups

When most of think about doing a backup, we think a SQL backup to disk or
tape - or for the more adventurous, using the capabilities of a third party
backup solution to do the same thing. Some combination of full, differential,
and log backups (you ARE doing log backups aren't you) gives us the ability to
recover if something bad happens.

Bad usually falls into two categories. The first is hardware. Something
happens to the server that forces you to restore or maybe even rebuild. The
second and more common is user error. Good applications usually do a pretty good
job of limiting the amount of damage a user can do (one common way is by just
marking items as deleted, not actually ever deleting anything) but it can still
happen. It can also happen at the developer level, something that makes it
through whatever flavor of QA you have in place. Where I work we have a 'in
between' scenario. We have a team that does nothing but process data received in
a zillion different formats and load in into our system, they also do
maintenance on existing data. That maintenance might consist of updating 100k
rows because we got wrong values from the client initially. We try to
standardize those operations, but obviously they represent another opportunity
for error.

Still, a good backup plan covers all of that, right?

Ok, you're right, it does. But let's think about what it means and how it
works. Let's say one of these power users runs an update that updates the wrong
column, sets the wrong value, leaves off the where clause. Something bad.
They call you, but the query has already completed. You can't actually bring the
database down because you've got other employees working against it (billable
time you know!), so you need to:

Restore your last full backup (how long does it take to restore?) to a new
database (do you have the space?)

Restore all the transaction logs up to 30 seconds before the mistake
happened (more time)

Join original table to new table in the restored copy, figure out what
action or actions will fix the problem (you probably have to do this, not
the person who caused the problem)

If your database is any significant size this is going to take a while. I'd
bet half an hour minimum from the time you learn about the problem to having the
data corrected. If the db is large, it could be longer. You're tied up,
potentially users can't work or have bad/incomplete/incorrect data, you're
burning disk IO to get the restore done.

So....options? You could give the power users restore permissions and let
them do it. Just kidding. It's one thing to look at letting a user initiate a
backup, something else to let them do a restore on a production server.

If you're doing log shipping with a decent amount of latency, you can bring
the spare out of standby, set up a linked server to it, query across servers to
get the information you need. Potentially this is faster than doing the restore
but you need to practice it a couple times - if you don't stop the log shipping
before the bad data goes across...well, back to plan A!

If you're replicating to a reporting server you might be able to get the data
from there as well. Again it depends on your latency. Transactional replication
in a 100m switched environment is typically a few seconds. Worth considering
slowing it down to give you the option. If you're using a snapshot you may not
be able to get 100% of the data you need depending on the frequency of the
snapshot/how long after the snapshot the mistake happened. If you're using
transactional, go stop the distribution agent right away!

Logging changes to a history table (auditing) is a very valid technique.
You've got the data you need right there, just a matter of determining which
rows to join to and what to fix. Including hostname and username in the auditing
table can make this a lot easier. The tradeoff is the extra disk IO generated by
the triggers on each table and the each disk space needed to store the history.

Log Explorer from Lumigent lets you back out the change at the transaction
log level - assuming you own the tool and you're not truncating the log!

In most cases ALL of the above methods will require you to get involved to
fix the problem. Depending on your environment you may feel comfortable giving a
few key users access to the auditing tables if you went that route, or access to
the reporting server if you're using replication.

This last idea may surprise you and it's not workable in all cases. Have the
users make a copy of the table (or of the chunk of rows they are about to change
if the table is huge) in MS Access. A 'make table' operation in Access is
reasonably quick (compare to 'select into') and gives the user the ability to
join to a local 'backup' table if they want to verify the changes afterwards or
they need to back them out. Similar but not as simple to implement ideas would
be show them how to create temp tables or to run a server side process using
DTS/BCP that would make a copy.

As a DBA my primary concern is protecting the data and making sure the data
is available to the people that need it. Backups do that. Beyond that, it makes
sense to implement controls where ever possible to restrict the amount of damage
that can be done - requiring all data access be done via stored procedure for
example. Yet I know...and you should too...that mistakes will happen. If you
rely solely on a backup you're going to work harder and take longer to fix the
mistake than if you've thought through and have ready to go one or more of the
above options. You also have to recognize that being the only one who can fix
the mistake makes you the bottleneck. As long as you can be responsive, that's
ok. On the other hand, having someone that can fix 'minor' mistakes without
calling you...is that a bad thing?