July 24, 2014 at 1:40 am
Hi guys,
How often should I test my backups? We do full backups Weekly, every Sunday. Daily for differential and hourly for trn.
Should I test my backup every time it finishes?
I know its a silly question but we do not have ample time to do it. Is there any other way to test my backups other than restoring it?
Thank you
July 24, 2014 at 4:39 am
Hi White.
I would recommend to test backups once a month (one from each instance/server). When I do this I restore and run dbcc checkdb to see if the backup is fine.
You should run weekly integrity checks on all your databases but that was not the question but I hope you do.
July 24, 2014 at 11:05 am
whitesql (7/24/2014)
Should I test my backup every time it finishes?
Yes.
Until you've tested it, you don't know whether it's restorable. It should not be a manual process (and so lack of time is not an excuse), it should be an automated process, all backups are automatically copied, restored, CheckDB'd and any errors mailed to you.
If you can't, then you need to review your backup retention to ensure you are never dependent on a single untested backup. CheckDB frequency should also be dependent on your backup retention time, there's no one-frequency-fits-all. Make sure that you retain enough backups so that you can always restore to a (tested) backup if corruption is found.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply