Usually when I’ve create a database backup, I’ll use the backup file name that represent database name, backup type (Full,Differential or Log), and also date and time stamp when that backup created. But what if there is someone else in your team, took that database backup with different name pattern? How do you know if that backup files is Full Backup, Differential Backup, or Transaction Log backup?
The answer is quite simple, it is RESTORE HEADERONLY command. You can use new query window from SQL Server Management Studio or using powershell Invoke-Sqlcmd cmdlet. Actually you can run this command using sqlcmd.exe utility but its result is quite hard to read so I recommend to use two option above.
RESTORE HEADERONLY FROM DISK='F:\DB_BACKUP\AdventureWorks2012.bak' GO
If you run this command using Powershell, your script will be look like this :
$RestoreCommand = "RESTORE HEADERONLY FROM DISK='F:\DB_BACKUP\AdventureWorks2012.bak' Invoke-Sqlcmd -ServerInstance <instance-name> -Username <username> -Password <password> -Query $RestoreCommand
The above command will give same result even if you run it using SSMS or Powershell, but with different output format. In SQL Server Management Studio, the result will look like a table while in powershell, it result will look like the following picture :
Focus on BackupType, it will show number between 1 – 8 with the following explanation :
|1||Full Database Backup|
|2||Transaction Log Backup|
|5||Differential Database Backup|
|6||Differential File Backup|
|8||Differential Partial Backup|
This information will let you know what type of your database backup files.