We have many of our SQL Servers where we take the backup locally on the server as well as over the network to other server in case of disaster.

Also, we have a process set that server support team takes those backups on drives for a defined time out to tapes.

Recently we had an issue where server went down and didn't had the backups on its alternative server and so needed to recover them from tape. Initially even from tape that could not be located, something was a miss for sure but we got it finally.

So instead of being dependent on other team, is there a way in SQL may be via script we can get a report or build something that confirms backups are going successfully onto the tape and when?

Please advise, if it can be achieved. Thanks!

  • You have to check the ARCHIVE a bit on the file attributes. You can easily do it using powershell. – Kin Shah May 6 '15 at 12:26
  • 2
    The only way to be certain you can recover from backup is with a RESTORE. It's a good practice to test your recovery plan periodically against a test instance. The details depend on your tape backup solution. Ideally, the restore process can be scripted so that it is repeatable and reliable. – Dan Guzman May 6 '15 at 12:34
  • You can check backup history tables there would be an entry and if you run Exec sp_readerrorlog 0,1,'VIRTUAL_DEVICE' and it returns some information. Read thoes information whenever backup happens on tape drive backup message is dumped in error log with location as virtual device. – Shanky May 6 '15 at 13:14
  • @Shanky if you are using third party backup software like redgate's sql backup pro then it will use VIRTUAL_DEVICE --> VIRTUAL_DEVICE: {'SQLBACKUP_guid}. Also, scanning error log on a busy PROD system will be an additional load. – Kin Shah May 6 '15 at 14:30
  • @Kin Ya correct but VIRTUAL_DEVICE keyword would be there all I wanted to say is if backup happens successfully using TP tool virtual device keyword is also a way to find it. Depending on size of errorlog it might have to scan some big chunk, in that case seeing backup history is better option. But frankly speaking I am not sure about load it will cause if it has to scan. – Shanky May 6 '15 at 14:43

If you want to be sure that you're backups are going to tape, you need to periodically recall the tapes and restore the backups. We recall tapes once every three months to do this, it seems like a lot of effort but it is not overkill if you value having offsite backups.

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.