January 11, 2016 at 8:23 am
How many times have you heard that you really don't know if a sql backup is good until you restore it? Fair enough but, in practice, I don't have the time to "test" each of the 100+ backups created nightly. (I'm usually so busy weekdays that any testing gets pushed off to the weekend. Not exactly timely).
What is your approach to checking backups? (Throw hands up in the air? Assign it to a junior DBA? Take one "role model" backup from each server and restore it?)
TIA,
BD
January 11, 2016 at 8:29 am
Barkingdog (1/11/2016)
What is your approach to checking backups?
Automate it.
You shouldn't be doing it. The junior shouldn't be doing it. A job should be restoring each backup to a test server, running a CheckDB and then dropping the resultant DB (unless it's needed for another process) and mailing you only if something goes wrong.
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
January 11, 2016 at 9:04 am
Automation is your bestest buddy.
You're not going to test every backup every night. Start with the vital ones. Figure out how often you can test them, more is better. Automate those. Then start working out how to automate testing the other backups in batches or in sample sets. But test as many as you can, just automate the tests. You sure don't want to have to run them manually.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2016 at 12:36 pm
Very good ideas! Will explore.
Thanks Gila and Grant
BD
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply