Skip to main content

Backup Horror Story

A business associate just spent two weeks in turmoil trying to recover data that was permanently lost. Their backup process was flawed. I've got a gnawing sense that this could happen to us too? What can I do to ensure my backups are occurring when they should and that they will be usable if I actually need them?

First of all, let me say -- Congratulations. It's better to have that gnawing worry and do something about it than to be caught off-guard sometime in the future with no good backup options. Data loss affects your reputation with customers/partners. It costs you staff time and money.

Data backups epitomize the mantra that "an ounce of prevention is worth a pound of cure." In the next paragraphs are some constructive way of thinking about the problem and recommendations for putting safeguards in place.

Causes for Data Losses

First, let's think about the different ways that data loss can happen.

This is probably the cause that immediately comes to mind. This can happen instantly or more insidiously over-time. If it happens over-time, it may silently corrupt parts of your database. You may need both a recent and retrospective data backup and will immediately wonder: how far back in time do I have valid backups?

Whether through physical removal or malware -- overnight your entire database server is effectively removed from the premises. In these situations, you are concerned about data recovery and getting a server online and configured. You will have lots of people eagerly wondering: "just how quickly can you get a database server stood up in our network?"

This is the most frequently occurring cause of data loss. Many people don't appreciate the options for recovering data that exist for SQL Server-based systems. A thoughtful backup and staging recovery server can greatly reduce the pain from accidental deletion of individual records in a database. In this case, you are not doing a full over-write / restore. Instead, you are performing a selective retrieval of content from a valid backup.

Backup Solutions

Is that gnawing sensation growing stronger? Good -- take action by answering these questions:

If your immediate response is "Zero data-loss, Zero downtime" you need to have a conversation that goes beyond data backup. These are called high availability features and include SQL Server replication, mirroring or log-shipping. If that is in within your means, we can help you configure hardware or cloud services to establish this. You have cheaper and simpler options if you can tolerate an hour or two of downtime. So think carefully about what amount of time and data loss you are willing to tolerate. It will guide you in all subsequent steps of designing a backup strategy.

That means 3 copies, in 2 different storage mediums 1 of which is physically off-site. Online backup destinations have made generating offsite backups a trivial matter. But don't forget about the need for a 2nd medium. You can achieve that easily by generating an encrypted, compressed backup burned to DVD or Blu-Ray disk every day or week. Optical storage is stable, it can't be accidentally over-written and is cheap insurance. Just make sure that as part of the SQL Server backup job you encrypt the content. Then store that media in a physically secure location.

All too often, a backup strategy is designed but not consistently tested. Server credentials change, database sizes grow -- a myriad of things can silently break the backup/restore process. You want to discover this problem before the rest of your company is out-of-the-water counting on you to "fix the database." That is why you should commit validating your backup and restore method. This should be repeated on a schedule that recurs throughout the year. Make a recurring entry on your calendar to remind you. And know that SQL Server allows you to script all of this. It does not have to take long to do. But it is critical to have a human-being eyeball the end result and confirm it was successful. The best approach is to have an end-to-end test environment that you can bring a restored database up. And then view recent record activity to confirm you have a valid restore method. Did your database server get malware-hacked? No problem, because you've got a pristine SQL Server virtual machine you can restore. With forethought, you can ensure no data loss and only an hour of downtime to get things back and running.

There is no predicting when data failure may happen. If work is documented properly, you can ensure that multiple different people are capable of restoring the system. In a crisis, your team will welcome detailed written explanations about what backups are available. Make sure you document what scripts and virtual machine images are available to accelerate the restore process.

A side note on virtualization: Many people take comfort in knowing that their IT staff have virtualized key servers including the database server. They may not understand that a virtual machine snapshot is not a flexible or effective substitute for a database-specific backup. Virtual-machine backup should not be your only strategy for data recovery. Native SQL backups and transaction logs are the simplest and most reliable backup source. They greatly improve the resilience and flexibility of your recovery options.  

Gnawing, Be Gone

SQL Server has the backup and high-availability features to ensure your company will not lose data. But this technology is no substitute for thoughtful design, documentation, and simulation of the backup/restore process.

If you have staff comfortable with implementing the things discussed here, use this article as a point of conversation with them. How do your existing methods match what is recommended here? In what ways could they be strengthened?

When you want a second pair of eyes on the problem, give us a call. Protecting customer data is the most fundamental thing we do. You may find our database administration page offers some additional insight on these topics.

Related Scenarios: