Backup Question

 

Well this may be sort a cheating here but I posted a blog on backups about in August. John to a look at it near the start of February and had a question. I liked the question and thought that I will post it as a blog entry.

 

Question

“Do you have any idea how common this is for a SQL backup to not be valid? Our company installs software at hospitals that includes a SQL Server DB. The DB gets backed up every day. That would be a bummer if the hard drive crashed and then the backups were corrupted, so that all data was lost. Is this something that we should be checking for at all of our sites, or is it very rare?”

My Answer:

“John, A lot is going to depend on the version of SQL Server. Versions that are older then 2000, I would be checking often if we had a good backup. I have been working with 2000 for some time now over a number of servers. I think I have run into only one backup that was not good. So if you are newer then 2000 I think the chance is not very high.

But by the way… there may be more pieces to the puzzle then just the SQL Server backup. You are going to want to run tests on a regular basis no matter what, I just talked to someone not to long ago, I believe Brent Ozar or Kevin Kline who said he knows a couple of shops that don’t even take the backs up to tape ever, now its a SLA from my understanding, but something to think about.

No matter what the final destination is of the backup, lets say it’s an off site storage facility and its on tape you are going to want to make sure that those backups remain good. Backups now are a many step process and I believe each step should be tested for the ability to recover, how long it takes, and the number of people that it takes to recover.”

When I talk to people on Disaster Recovery I try to make it a point that it may not only be SQL Server that you need to validate or keep an eye on. For example, I worked at a location where the databases were being backed up to tape but the exchange servers were being backed up to tape as well. There would be times that we could not be 100% sure that all our backups made it to tape before the disk copy was removed. At this same location they would not spend the money for years to upgrade their backup system on their main production system. I found this to be a rather sore spot with me because of a few things. One it was a public company, two it was my job to make sure we could restore the data but the left me in a place where I could only guarantee that it made it to disk.

As for my blog, you should start seeing me post a couple times a week now, I am back from vacation and I am ready for some sleep and to get back to work.