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.