November 22, 2013 at 3:18 pm
Is there a way to differentiate between a backup and a restore in the backupset?
Erin
November 24, 2013 at 1:12 pm
Only backups are listed int he backupset table. Restores are tracked in the restorehistory table.
November 24, 2013 at 10:14 pm
I believe that restores are indeed recorded in to the backupset table.
A good blog on this is http://shaunjstuart.com/archive/2010/12/msdb-backupset-table-peculiarity/
Backupset it seems, simply holds the details of a backup whether it was a backup taken on that instance or a backup restored on that instance. You may have the case where you do a backup on that instance and then restore the same backup which will mean there is only one row for that backup in the backup set.
Or if you did a backup on another instanceand then restored to your current instance, you will find the restore creates a row in the backupset table with the details of the backupfile.
If it were me...
To list backups taken on an instance, I would query the backupset table where server_name = @@SERVERNAME
To list restores done on that instance, I would query the backup set table and join to the restorehistory table on backup_set_id
Hope that helps. Someone may have a better/clearer answer for you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply