1

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.

  • 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 '18 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 '18 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 '18 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 '18 at 13:59
4

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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