backupset question

  • Is there a way to differentiate between a backup and a restore in the backupset?

    Erin

  • Only backups are listed int he backupset table. Restores are tracked in the restorehistory table.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • 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