Was that backup file taken through Native or third party tool?

On Production Instances it is common to take the SQL Server Database backup through third party tools. Until SQL Server 2008, only the third party tools provided the most important feature, Backup compression. These tools also provide backup encryption along with compression.

At times, the DBA needs to take ad-hoc database backup manually in cases where a Change Control to be deployed. When the disaster strikes, it will be little tedious to find out whether a particular database backup was taken through the Native SQL tools or through the third party backup utility.

In such situations, I use the RESTORE HEADERONLY command to quickly check if the backup was taken through the third party tool or not. Here is a sample script.

RESTORE HEADERONLY FROM DISK = 'C:model.bak'

If the file was taken through SQL Server Native tool, this command would return the header information correctly.

If the database backup was taken through a third party tool, this command usually fails with the below error.

Another approach is to query the backupset table in MSDB. The values in the description and name can also be used to distinguish between native and third party tool backups. While taking manual database backups, we usually don’t enter the description for the backup set. Hence usually this column will have a null value for database backups taken manually. Also some tools maintain the separate software_vendor_id for the database backups taken through them.

Please feel free to share if you are following a different approach to verify the tool through which the database backup was taken.

6 thoughts on “Was that backup file taken through Native or third party tool?

  1. Manish

    Hi Pradeep,

    Bit confused here. Eventhough I have backup through third party tool when I run RESTORE HEADERONLY FROM DISK = ‘location of backupfile\filename.bak’ command I received correct header information. How can I know weather it is native backup or through thirdparty tool ?

    Thanks,

    Reply
  2. PradeepAdiga Post author

    Hi Manish,
    In your case, I assume the third party tool does not compress the database backup using its own algorithm. Does the second approach that I mentioned to query the backupset table, help you to distinguish between backups?

    Reply
    1. Manish

      Hi Pradeep,

      Appreciate your time and effort.

      I tried the second approach as well but in backupset table I am getting same name and discription as I have backup through third party tool.

      Let me be clear third party tool is there for backup compression.

      Is it possible that If the third party tool is installed for backup compression eventhrough we try to take backup for single database manually will use same compression algorithm.

      Regards,
      Manish

      Reply
  3. PradeepAdiga Post author

    Manish,
    Ok. When you take the database backup through the third party tool, it has a distinct description in backupset table. But when you issue a BACKUP DATABASE command in SSMS, the description field will be NULL, unless you specifically mention in the command. That is how you can differentiate between Native and Third Party tool backups.
    May be the words that I used in my post were ambiguous 😉

    Reply
  4. Ava

    Hello Pradeep –

    Is it possible if I ask you to share your backup and recovery documentation for your production envirnment? I would like to see how others implement it so I plan mine accordainly, I understand each envirnment. My goal is to review how others put together their document.

    Reply

Leave a Reply