February 9, 2010 at 3:15 pm
I manually ran RESTORE VERIFYONLY on a few of my full backups today, just because I've never done it before and wanted to see the results. 🙂
Does anyone else do this on a regular basis?
If RESTORE VERIFY indicates a problem with a backup, what should be done to fix the problem?
It appears that there is no return value like you would get from a stored proc or function, just a message that looks like the output of a PRINT statement. If I wanted to run RESTORE VERIFY on my backups in some sort of automated fashion, I'm thinking I'd have to parse the returned message. This seems a bit clumsy. Is there a better way?
February 10, 2010 at 1:11 am
Let me know if you find a solution to this. I was also looking into automating the RESTORE VERIFY.
February 10, 2010 at 5:14 am
Dave Mason (2/9/2010)
Does anyone else do this on a regular basis?
If you don't verify your backups, it means you have no idea if they will restore when necessary. Means you could have a disaster situation, confidently reach for the backups only to have them fail
If RESTORE VERIFY indicates a problem with a backup, what should be done to fix the problem?
Take a new backup.
Just note that RESTORE VERIFY does not actually check the entire file, it checks some headers. It's possible for RESTORE VERIFY to succeed and RESTORE to fail. I believe the exception to that is when the backup is taken with the CHECKSUM option.
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 12, 2010 at 3:34 pm
Dave Mason (2/9/2010)
It appears that there is no return value like you would get from a stored proc or function, just a message that looks like the output of a PRINT statement. If I wanted to run RESTORE VERIFY on my backups in some sort of automated fashion, I'm thinking I'd have to parse the returned message. This seems a bit clumsy. Is there a better way?
I have come to the conclusion that if a backup cannot be verified, the RESTORE VERIFY statement will generate an error along with the message (probably via RAISERROR). So there is no need to parse the returned message as I thought.
I created a sql job that runs RESTORE VERIFY on certain backups. When RESTORE VERIFY fails, it causes the sql job to fail. If RESTORE VERIFY successfully verifies the backup, the sql job succeeds. This is straightforward and easy to implement with a backup plan.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply