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.
Legacy Comments
|
crazyjoe
2004-02-26 |
re: Test your Backups! 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! |
|
Tara
2004-02-26 |
re: Test your Backups! 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. |
|
crazyjoe
2004-02-26 |
re: Test your Backups! I guess it depends on how much data your boss is willing to lose in the event of a failure, huh? |
|
Tara
2004-02-26 |
re: Test your Backups! 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. |
|
Brett
2004-02-27 |
re: Test your Backups! I test 'em every night.... |
|
AjarnMark
2004-02-27 |
re: Test your Backups! 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? |
|
Tara
2004-02-27 |
re: Test your Backups! 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. |
|
Lisa
2004-03-19 |
re: Test your Backups! 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 |
|
AjarnMark
2004-03-19 |
re: Test your Backups! 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. |
|
Lisa
2004-03-19 |
re: Test your Backups! 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 |
|
Lisa
2004-03-19 |
re: Test your Backups! 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. |
|
AjarnMark
2004-03-19 |
re: Test your Backups! 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. |
|
Lisa
2004-03-19 |
re: Test your Backups! 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. |
|
Lisa
2004-03-19 |
re: Test your Backups! 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. |
|
AjarnMark
2004-03-19 |
re: Test your Backups! 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. |
|
Lisa
2004-03-22 |
re: Test your Backups! 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 |
|
Lisa
2004-03-25 |
re: Test your Backups! 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 |
|
Lisa
2004-03-29 |
re: Test your Backups! 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 |
|
AjarnMark
2004-03-29 |
re: Test your Backups! 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. |