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, 2015 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, 2015 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, 2015 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, 2015 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, 2015 at 14:43

1 Answer 1


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 and acknowledge you have read our privacy policy.

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