How to find whether backup is performed using SQL native methods or third party tools?
Interesting question: how can we know the difference between a native backup and backup taken by third party tool?
Say you have started newly in a enterprise wide platform and your task is to find out what methods of BACKUP and RESTORE are followed to take care of data which is a prime task for every DBA. Usage of third party tools has been increased since last few years to take advantage and flexibility from SQL Server. This is good upto some extent where the tailor-made features are not available with Enteprise Manager or SQL Server Management Studio (this is better now).
So here is the trick to find the prime task to see whether they are performing backups using the SQL Server native methods (BACKUP/RESTORE) or any third party tools usage. Using the following to command to distinguish between native backup or thirdparty tool:
RESTORE HEADERONLY FROM < path of file in backup_device >
Refer to the values returned on SoftwareVendorId and so on:
| SoftwareVendorId |
int |
Software vendor identification number. For SQL Server, this number is 4608 (or hexadecimal 0x1200). |
Software vendor identification number |
| SoftwareVersionMajor |
int |
Major version number of the server that created the backup set. |
Major version number of the software that created the backup set |
| SoftwareVersionMinor |
int |
Minor version number of the server that created the backup set. |
Minor version number of the software that created the backup set |
| SoftwareVersionBuild |
int |
Build number of the server that created the backup set. |
NULL |