<< Separate Development, Testing, and Live Data | Home | Source Control and the Build Process >>

Test your Backups!

You DO backup your databases, don't you?  And you do test your backups occasionally to make sure they're good, don't you?

One of my clients didn't, and they got bit hard recently.  Thankfully it was “just” the test instance.  But it set the QA department back several weeks, which then affects everyone's deadlines.  Oh sure, they were doing regular nightly backups, but apparently nobody verified that the backups were usable.  Then disaster struck, and the entire test environment was gone!  The nightly backups weren't any good.  The monthly backups weren't any good.  And so on, all the way back to the Autumn of 2002.  You might have noticed that I'm posting this in early 2004.  That's a year and a half back they would have to go to recover, and then re-apply all updates made since that time.  This would be a good place to argue that they'd be better off making a fresh QA instance from the production one, and that might be true, but in this case that's no easy chore either.

Regardless, all this pain and suffering could have been averted if they had had good backups.  You can bet they do now.

posted @ Thursday, February 26, 2004 12:30 AM

Print

Comments on this entry:

# re: Test your Backups!

Left by crazyjoe at 2/26/2004 7:44 AM
Gravatar
This reminds me of a story my trainer told me back when I was learning SQL Server. The company he was training for never checked the backup of their customer billing database. So of course, one day it went down and they tried to recover it.

The backups were no good. Needless to say this was a VERY bad situation, since all of the billing info about all of their clients (and they are a huge company, lots of corporate clients) was gone.

They ended up having to bite the bullet and pay a data-reconstruction company to pull the data off the hard drives. Pretty expensive.

The trainer never mentioned whether they fired the DBA (or if they even really had one...plenty of companies look at the easy-to-use front end of MS SQL and think that someone familiar with Access can manage the DB).

Yikes. Great tip, Ajarn!

# re: Test your Backups!

Left by Tara at 2/26/2004 10:21 AM
Gravatar
I wish my boss would allow me to write some scripts that test the backup each night. Right now, he's got our junior DBA manually testing them every other week. Every other week isn't often enough in my opinion. Maybe I'll write the script anyway just so that I have them around.

# re: Test your Backups!

Left by crazyjoe at 2/26/2004 12:47 PM
Gravatar
I guess it depends on how much data your boss is willing to lose in the event of a failure, huh?

# re: Test your Backups!

Left by Tara at 2/26/2004 4:16 PM
Gravatar
Well, we've got log shipping going to our disaster recovery site. Data loss should be less than 15 minutes. If the transaction log backups were bad, the restores on the secondary server wouldn't work anyway, so we'd be alerted within an hour of a problem. So worst case, we'd have to use the full backup that was tested two weeks ago and all of the transaction log backups since then. Not so bad since the commands can easily be scripted.

# re: Test your Backups!

Left by Brett at 2/27/2004 8:11 AM
Gravatar
I test 'em every night....

# re: Test your Backups!

Left by AjarnMark at 2/27/2004 9:53 AM
Gravatar
Tara and Brett, I hope you're planning to write something about how you go about testing your backups to make sure they're okay. Do you merely do a restore somewhere and figure if the restore doesn't crash, then you're okay? More in-depth testing after a restore? Some other tip or trick without having to actually run a restore?

# re: Test your Backups!

Left by Tara at 2/27/2004 10:53 AM
Gravatar
Yeah, yeah, yeah, in time I'll get the info out there. Not the full script but bits and pieces and explanations. Here's a start:

SELECT bs.database_name AS DatabaseName, MAX(bms.physical_device_name) AS FullBackupName
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s ON bs.database_name = s.name
WHERE CONVERT(VARCHAR(20), bs.backup_finish_date, 101) = CONVERT(VARCHAR(20), GETDATE(), 101) AND
s.name NOT IN ('master', 'msdb', 'model', 'pubs', 'Northwind')
GROUP BY bs.database_name

The above query shows you the last full backup on your user databases. Use that to determine which file needs to be tested.

# re: Test your Backups!

Left by Lisa at 3/19/2004 7:00 AM
Gravatar
I am so thrilled with all the posts in this topic. Thx to all.
I am doing a back up on my SQL Server 2000 but I don't know how to test. Can someone please share your experiences? Any pointer is greatly appreciated.
Lisa

# re: Test your Backups!

Left by AjarnMark at 3/19/2004 8:54 AM
Gravatar
Lisa, if you have another SQL Server, or a separate Instance (SQL 2000) that is a great way to test your restore. Restore the backup to that other server/instance and then run a few data validations such as record counts on key tables, aggregates (sums, averages, min, max, whatever makes the most sense for you data) on key data.

If for some reason you're without benefit of a separate server or instance then you can restore to the original server but under a different database name. Before attempting this, you really need to read BOL and understand the command options so you don't accidentally overwrite your live database. I really don't like this option and encourage you to get another system you can use instead, but in a pinch, it's an option.

# re: Test your Backups!

Left by Lisa at 3/19/2004 9:18 AM
Gravatar
First of all, thank you very much for your information.
I am not an expert in SQL Server at all so please bear with me for a few minutes here. I know some simple commands such as insert/update/delete and aggregates.

How do I know if I have another SQL Server, or a separate Instance?

When I open up EM, this is what I see:
-SQL Server Group
+Local (Windows NT)
+Live (Windows NT)


I have SQL Server (LIVE) and SQL Server (LOCAL) and I do most of my tests on LOCAL. I would like to learn how to do a back up on my LOCAL but so far, I have not achieved it yet. I am not sure if you refer these as a different SQL Server or not.


I hope I can do a screen capture of my SQL Server (EM) so you can see better but there is no attachment capability here so....

Thanks again,

Lisa

# re: Test your Backups!

Left by Lisa at 3/19/2004 10:54 AM
Gravatar
No, I don't have a separate machine.
I have been reading BOL for 2 days (that's how I learn how to do a back up and restore) but I honestly don't see anywhere in that BOl that teaches me how to test my backups.

# re: Test your Backups!

Left by AjarnMark at 3/19/2004 11:24 AM
Gravatar
Lisa, you are correct that BOL doesn't teach you how to test your backups. That's because there is no specific command to do it, rather it is a business procedure that you have to determine for yourself. The first key component of which is to do a RESTORE into a different (preferabley new) database name. And then you will want to run some data analysis to validate that the restored data is complete, which is where the aggregates (count, sum, etc.) come in. But only you can determine which tables/data to analyze in order to have a comfortable feeling that the backup was complete.

Some people just perform a RESTORE and assume that as long as they didn't get an error message, then everything is fine. But most people want to actually run some SELECT statements on the restored data to validate it's wholeness.

# re: Test your Backups!

Left by Lisa at 3/19/2004 11:34 AM
Gravatar
Hhahahhahhaha, your message makes me feel so much better now. So, let me start running some testings and will let you know how it turns out. Double thanks.

# re: Test your Backups!

Left by Lisa at 3/19/2004 11:41 AM
Gravatar
Oh, let me ask you one more question. After I created my back up db. It created a file (.bak) that is stored in my local drive. When I double click on that file trying to get it open (I chose NOTEPAD to open) but I cannot read the stuff in that file. It displays some unreadable characteristics. Do you know what that file is or how it is used? Thx.

# re: Test your Backups!

Left by AjarnMark at 3/19/2004 2:55 PM
Gravatar
Lisa, that's the actual backup file which you would use to restore the database. For example, if your server died, but you had a copy of that file on CD or tape, then you could install a blank SQL Server on a new machine and issue the RESTORE command, using the .bak file as the source. I would expect it to be in a binary format and not readable by another program.

# re: Test your Backups!

Left by Lisa at 3/22/2004 11:00 AM
Gravatar
Many thanks to AjarnMark.
I learned a lot from you. I finally get the idea of backing up and restoring. Thanks again for your help.
Lisa

# re: Test your Backups!

Left by Lisa at 3/25/2004 12:58 PM
Gravatar
I have been testing my back up and restore and it seems pretty straight forward. However there is another issue that keeps bugging me.

There are 3 type of back ups:
1) Full back up (back up the entire database)
2) Differential back up (back up the new and changed data)
3) Transaction log (back up the record of all the changes made to db)

Test 1
I created 1 table that has 1 row.
I did a full back up
I then went deleted this db
And finally I did a restore and it restored my table with 1 record. Great.

Test2
I added another row into this table (so now I have 2 rows).
I went back and deleted this table. Remember this time I didn't do a back up.
Then I did a restore and it restored ONLY the the first row.

My question is: Do you have to back up your db every single time you make a change to your db in order to save the new changes? What does "scheduling" do anyway?

Thank you,

Lisa

# re: Test your Backups!

Left by Lisa at 3/29/2004 6:53 AM
Gravatar
Hi AjarnMark,

Thx for your quick response.
I did post my question on sqlteam.com under 'ASK SQL TEAM' link last Friday. How do I view my question or see if there is any response to my question so far?
Thx.
Lisa

# re: Test your Backups!

Left by AjarnMark at 3/29/2004 9:29 AM
Gravatar
When you post a question using the Ask SQLTeam link, you'll receive an email with a link to your question's discussion thread once the moderators have had a chance to review and post it.

Another option is to register with SQLTeam and post your question to one of the forums directly. You also have the option of subscribing to a thread so that you'll receive email notifications when someone responds.

# T-SQL script to copy the last full backups of the user databases to a remote server

Left by Tara's Blog at 4/2/2004 5:41 PM
Gravatar

# T-SQL script to copy the last full backups of the user databases to a remote server

Left by Tara's Blog at 4/2/2004 5:44 PM
Gravatar
Comments have been closed on this topic.
«May»
SunMonTueWedThuFriSat
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678


AjarnMark

Source Control 2.0 from Red Gate

Emerald City Domains


Professional Association for SQL Server

About
Article Categories
Archives
Post Categories
Image Galleries
Some Favs
Syndication:

RSS