Click here to monitor SSC
  • Av rating:
  • Total votes: 14
  • Total comments: 2
Douglas Reilly

Why bother with backup

25 April 2005

First in a series exploring major facets of SQL Server backup

Backing up SQL Server data is like many of the things we do because we figure we need to. It is good for you, like eating a good diet and getting exercise. Unfortunately, folks are often about as successful with SQL Server backups as they are with diet and exercise.

This is the first in a series of articles covering SQL Server database backup. The series starts from the very basics of why database backup is important. The question of why to backup a database can inform many other decisions.

Backup to prevent being fired

The first major reason for backing up a database is that you could be fired (or face some other serious career problem) if you do not. The web is full of stories of folks who have been fired because they didn’t backup a database. A Google search for “fired because of no database backup” returns about 145,000 links, a number of which go into details. Simon Galbraith of Red Gate Software reports the following story:

I got my first proper job in 1992 and looking back, the most amazing thing about it was the high quality of the IT. We had fully functional Windows-based email, active bulletin boards, properly organized network drives, and all the things that we take for granted now in 2005. It all worked pretty much as it should too. It was five to 10 years ahead of its time.

There were 300 people, mainly PhD physicist types, in the company and two people managing all of their IT needs. Mike, the unassuming man in charge of IT, was considered a god.

About 6 months into my time at the company, there was a server crash; the server wasn’t properly backed up and the CEO lost some important information from the corporate database. That same day Mike made the long final walk through the office with a black bin liner full of his possessions, escorted by the bleak-faced harridan from HR.

In the 18 months I worked there, Mike was the only person sacked for incompetence, which anyone who worked there would agree was a sick joke. It is an important IT lesson, though—no matter how good you are at the rest of your job, if you lose data your job is going to be on the line. Sure, backup is important from an organizational point of view, but it is perhaps more important from an employment point of view— it is one of the few times an employee can be categorically proven to be incompetent and when senior people take an interest in things that wouldn’t normally concern them.

Here’s my story: I once worked with a very skilled programmer, Alan, who was working alone and off site on a very important program. The programming was going well, and everyone was thrilled with Alan’s work. When he announced sheepishly that he had lost his hard disk, and had no backup of any of his work, he expected it to be a pretty bad day. It was worse than he thought.

Our boss, the normally unflappable Steve, was speechless. Literally. He could not talk for a couple of hours, and asked Alan to go away. Eventually, it was decided that Alan would be given some time to redo all his work. Alan did get the work reconstructed in the appropriate time frame, and went on to build what almost everyone acknowledged as a better version of the software. That said, it involved a number of sleepless nights and a large amount of unpaid overtime.

A number of the articles online about firings related to database backup failure are from clearly upset former employees, and I daresay they are not completely objective. Interestingly, at least one reports that a firing of an employee resulted from another employee not having done a database backup. A developer was copying a database from one server to another, and in a series of events that make Murphy of Murphy’s Law look like an optimist, the source and destination database ended up becoming corrupt. While this would not have caused a terrible problem with proper backups in place, there were no backups. Rather than firing the person responsible for the backups, the company fired the programmer, who did not have database backup in her job description.

Suffice it to say that I cannot imagine a scenario where you would be fired for actually backing up too much or too well.

Backup to keep the company alive

Beyond being a cause for firing, a serious enough backup failure could cost your company its very life. Can you imagine your online company being totally unavailable for a day? In July 2003, it happened at Orbitz. Fortunately, in the end Orbitz was able to get all of its data back online. While the cost of this sort of downtime is difficult to calculate, I think we can say safely that it is quite large, and any company already living on the edge might not be able to survive. Were backups not in place, one can only imagine how much more devastating the effect would have been.

The National Archives and Records Administration reports that 93 percent of companies that lost their data centers for more than 10 days filed for bankruptcy within one year.

Backup because it is the law

There are certain types of data that need to be backed up because maintaining the records are required by law. Certain records are required to be maintained in the U.S., for example, by the Internal Revenue Service (tax related) or the Security and Exchange Commission (stock ownership related). Laws vary by country, but in general some bits of data are so important that there are legal requirements that they be available, with company officers or others possibly being held personally responsible for not taking proper action.

Backup because lives are on the line

A few years ago, I worked for a company that published a golfing magazine and web site. A number of the people who started the golf-related company had worked together in a healthcare setting; I had previously been employed by the largest hospital system in the state as well as a home healthcare company. This was during the heady days of the dotcom boom, and with some regularity, we would do things that we could not imagine doing in our previous lives. When things went wrong, we would comfort ourselves with the phrase, “It’s only golf.” While many people consider golf to be a very important part of their lives, the reality was that even if all of our web and database servers went away, no one was likely to get hurt.

Not so in our previous lives. I worked on a pocket database system that enabled nurses to enter information on patient condition, including what were called inputs and outputs—how much fluid went in, and how much went out.

I was reminded of how important this job was when I was hospitalized (in a different hospital). After a very serious surgery, I was apparently not producing urine once my catheter was removed. This was not a good thing, and it was eventually decided that the catheter should be reinserted—on the floor, while I was awake, no pain meds, no nothing. It was only after that rather unpleasant experience that the staff discovered that my inputs had not been recorded properly, and I was not putting out urine because I had not been getting the IV fluids I should have. Although this particular problem was not caused by a lack of a database backup, it could have been. Other failures and near misses I have seen in the healthcare world remind me every day how critical it is to maintain the integrity of data.

How much backup is enough?

One of the peculiarities of database backup is that the most difficult data to keep backed up (data that has arrived in the last few minutes) is the data most likely to be lost. The following illustration shows the approximate relationship between how new the data is and how likely it is to be lost.

Very old data will almost certainly, even in the most sloppy environment, be available in some form. It might be on paper, in the form of copies of customer invoices, but it will be data that can be recovered. The very newest data may not be available at all in any form in case of a catastrophic database failure. And, this is the data you really need to have, since it is data you will be acting on in the immediate future.

Are you really backing up?

Recently I worked on a project that required managing a huge number of Word documents. The files could have been stored inside the database, but were instead stored in the file system. The files arrived for the system to manage via a secure FTP connection, and the name of the files contained all the information required to index the document, resulting in very long file names.

During testing, I was happy to see that the database was being backed up properly. I was less happy to discover that the Word document files, required by the system to function correctly, were being backed up onto DVDs, and in the process, the filenames were being mangled and truncated. The mangled filenames would not be the names the database was storing, and so the program would be unable to find the documents in a restored system. The end result was that although the database was being properly backed up, the system would not function with the restored Word document files.

There is no end to such stories. People discover that no one has changed the tape in the tape drive for months, or discover that the backup tapes are just not readable. So, before you dismiss this series because you are already doing backups, make sure you really are, and that they can really be restored.

Next: Exactly what is a backup, and how is it done?

In the next article in this series, I’ll discuss the various technologies that will ensure data security. I will also discuss how secure is secure enough. Before you say "I can afford no data loss, at all, ever," read the next article, as I explain just how much that might cost.

Douglas Reilly

Author profile:

The late Douglas Reilly was the owner of Access Microsystems Inc., a small software development company specializing in ASP.NET and mobile development, often using Microsoft SQL Server as a database. He died late in 2006 and is greatly missed by the SQL Server community as one of the industry's personalities.

Search for other articles by Douglas Reilly

Rate this article:   Avg rating: from a total of 14 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Links to article series
Posted by: Anonymous (not signed in)
Posted on: Tuesday, December 25, 2007 at 6:05 PM
Message: There's no link to the next article in the series.

Subject: nice article but not able to see any technical in it
Posted by: ashish.kuriyal (view profile)
Posted on: Thursday, February 11, 2010 at 3:41 PM
Message: i dont know either i am correct or not, but if you could provide more example like how data get recovered after failure would help in reading this article with more interest.

 
Simple-Talk Database Delivery

DLM
Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
Microsoft and Database Lifecycle Management (DLM): The DacPac

The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx), provides an... Read more...

 View the blog

Top Rated

Microsoft and Database Lifecycle Management (DLM): The DacPac
 The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx),... Read more...

A Start with Automating Database Configuration Management
 For a number of reasons, it pays to have the up-to-date source of all the databases and servers that... Read more...

Archiving Hierarchical, Deleted Transactions Using XML
 When you delete a business transaction from the database, there are times when you might want to keep a... Read more...

Rollback and Recovery Troubleshooting; Challenges and Strategies
 What happens if your database deployment goes awry? Do you restore from a backup or snapshot and lose... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.