February 21, 2009 at 2:33 pm
Comments posted to this topic are about the item Do You Verify Your Database Backups?
Brad M. McGehee
DBA
February 21, 2009 at 3:20 pm
I absolutely agree... It's not what you can backup... it's what you can restore. I do at least one "practice" recovery a week and I'm not the System DBA. I do it because I'm pretty sure that they don't do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2009 at 11:08 am
To be honest I am shocked to hear that people don't verify their backups.
I'm also surprised that it isn't the default action for a backup and requires a DBA to deselect it if they don't want to verify.
February 22, 2009 at 11:27 am
What backups?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 22, 2009 at 11:35 am
David.Poole (2/22/2009)
To be honest I am shocked to hear that people don't verify their backups.I'm also surprised that it isn't the default action for a backup and requires a DBA to deselect it if they don't want to verify.
What's worse is that, on 2000 and earlier, verify did very little. It checked the backup headers and a bit more. Passing verification was not a guarantee that the backup could be restored.
Even on 2005 and higher, I think you need to use the Checksum option for a verify check everything. Without that, the only way to check that a backup can be restored is to restore it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 22, 2009 at 6:11 pm
In 2000 I checked it, but didn't count on it. I tended to restore often enough to dev/test that I didn't worry about scheduling things too much. We did have a quarterly test on some of the more critical systems to be sure that we could do it, and we'd pull back from tape at least yearly if not more often to be sure that things were working correctly.
I think it depends on your environment, but you want to both practice your skills at restoring (so restore logs, backups, and to times), and also make sure your equipment is working.
February 22, 2009 at 7:44 pm
We're lucky. We don't have a true data warehouse so to provide reporting capabilities without impacting the OLTP we restore nightly the backups into the DW. The big thing missing though is the TLog and tape copies. The one time I tried to test the tape copies the tape was found to be corrupted!!!! I now just need to convince the system admin team (owners of the tape backups) that they need to put in place testing of the tapes. They say they have no room to test the recovery, I say a backup that cannot be recovered is not a backup. Discussions (arguments) continue ...
February 24, 2009 at 12:56 am
We test them daily as they are transferred and restored to another server.
This another server is called "the clone".
Therefore anyone (testers, developers, ...) can do tests, simulation, analysis,... on the data of the production environment without touching it.
JM.
February 24, 2009 at 3:18 am
We don't need no DBA 'round here checkin' our backups. We got that fancy Squeal Server that takes care of itself. Now git.
James Stover, McDBA
February 24, 2009 at 6:08 am
The backup is not complete until I've restored it on a test machine. I know not everyone has this capability but since I've always been on small teams that were doing development as well as maintaining the production enviornment I have always had a "development" and "Test" machine and can not imagine relying on a backup that has not been tested.. The best axiom is "It isn't what you can backup, it's what you can restore".
James.
February 24, 2009 at 6:21 am
I'll try the contrarian view today, just to see..! I wonder if a better question isnt "how often do you verify" your backups?
I manage a couple small businesses, and the reality is I test backups perhaps once a quarter. Losing a quarters worth of information would definitely hurt, but it would be survivable. Why don't I test more often? Time, and experience. I have a limited amount of time each week, and a lot of things to do - that combined with my experience telling me that bad backups are pretty rare, I can extend the risks I take, with an understanding of the dangers.
That's how it is with many small businesses. Even if you have a DBA, do they really need to verify every backup? Even if you do one a week, you potentially have a 7 day gap where you might have the ability to restore to point in time. Is that acceptable?
Why doesn't MS make verification automatic, or include a step in the maintenance plan for restoring a test copy and checking that it's valid?
February 24, 2009 at 7:08 am
I admit, I don't verify all my backups. Why. I support 80 + SQL Servers, am expected to support 2 large Oracle apps (Peoplesoft and Cerner), 4 Sybase servers, I suppose the My SQL is my baby, I have to pass .NET certification, write Access reports, Medical manager reports, support legacy HP-3000 apps, Star Vista reports (base on a MUMPS database...very interesting). And handle end user questions/problems.
Am I a disaster waiting to happen. You betcha. I think management doesn't think the dbas (2 of us) do much. What can you do but your best.
February 24, 2009 at 7:15 am
For us newbies - Would someone please give a hint as to how you verify a database backup ?
February 24, 2009 at 7:15 am
I test my backups on a daily basis by automatically restoring on a test server. Early on in my career I didn't do this, but read enough articles and forums like SSC that I got scared into doing it. 🙂
February 24, 2009 at 7:20 am
I do semi-random spot checks. I restore one or two backups a week to test them, picking different databases different times. I've never yet had one fail, but I still feel the need to spot check.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 56 total)
You must be logged in to reply to this topic. Login to reply