2

I'm using Tsql script to restore several databases.

RESTORE DATABASE XXX_M
   FROM DISK = 'C:\XXX.bak'
   WITH FILE = 1, replace
GO

Sometimes the .bak file comes corrupted (not data corrupted ).

I want to know if there is a way to check if the file is not corrupted then restore else don't. I've tried to use @@rowcount after restore VERIFYONLY but it's not good.

RESTORE VERIFYONLY 
   FROM DISK = 'C:\xxx.bak'
if @@ROWCOUNT !=0
print 'false'
else 
print 'good'

Any other ideas?

Thanks.

Improve this question
4
  • 4
    This is why you test your backups... that, ultimately, is the best test. If it's happening frequently shouldn't you check your storage?
    – DimUser
    Apr 23, 2018 at 12:11
  • What if you have 2,000 backups? will you test every one of them every day? It's happening not that frequently but still, I want to prevent from the database to restore unsuccessfully and been stuck on a single user.
    – DnL
    Apr 25, 2018 at 11:22
  • If you have 2,000 backups you should be able to automate testing restores pretty easily. You're not managing taking those backups manually, right? You don't necessarily have to test all 2,000 every night, but you could rotate so that every database is checked with a real restore every n days. There is no value in taking backups if you can't be certain they can be restored.
    – Aaron Bertrand
    Apr 25, 2018 at 11:43
  • The problem was not with testing backups, the backups are been restored every day. The problem was when the file transfer interrupted then the file (zip) is not complete. Thanks for the answers the script is good.
    – DnL
    Apr 25, 2018 at 13:59

1 Answer 1

Reset to default
6

Checking @@ROWCOUNT isn't valuable here because RESTORE VERIFONLY doesn't return any results. It either prints status messages or error messages. So, a simple TRY/CATCH should do:

BEGIN TRY
  RESTORE VERIFYONLY ...
  RESTORE DATABASE   ...
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE();
END CATCH

If the VERIFYONLY fails, it will raise an exception, and skip the RESTORE.

Of course, there are still going to be scenarios where VERIFYONLY will succeed but the RESTORE will fail (for example, something swoops in and takes all your disk space).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.