Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Alternatives to SQL Backups

By Andy Warren,

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?

Agree? Disagree? Add your comments below and tell me what I got right...or wrong!

Total article views: 8188 | Views in the last 30 days: 0
 
Related Articles
FORUM

Backup/restore

Backup/restore

FORUM

SQL Server Backup & Restore startergies

SQL Server Backup & Restore startergies

FORUM

Production Server DB backup Restored on Development Server Backup

Production Server DB backup Restored on Development Server Backup

FORUM

Backup and restore

Backup and restore

FORUM

SQL server 2005 Backup & restoration size

SQL server 2005 Backup & restoration size

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones