Backup & Restore

Check if a database backup is Full, Differential or Transaction Log Backup

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.

FROM DISK='F:\DB_BACKUP\AdventureWorks2012.bak'

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 :

BackupType Description
1 Full Database Backup
2 Transaction Log Backup
4 File Backup
5 Differential Database Backup
6 Differential File Backup
7 Partial Backup
8 Differential Partial Backup

This information will let you know what type of your database backup files.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s