SQL Backup files, distinguish partial and full backup files

Question

I have scheduled backups running through SQL Agent, with Full Backups nightly, and differential backups hourly. Is there a way to determine which of the backup files is the Full backup, and which is the latest differential?

Intuitively, it would seem the largest backup within 24 hours is the full, and the latest smaller backup is the partial. However, this isn't robust.

Is there a way to probe the backup file to check the backup type? (Preferably in c#)

Answer 1

USE

RESTORE HEADERONLY FROM DISK = 'C:\AdventureWorks.BAK'

Then check the BackupType

1 = Database
2 = Transaction log
4 = File
5 = Differential database
6 = Differential file
7 = Partial
8 = Differential partial

Referrences

• http://msdn.microsoft.com/en-us/library/ms178536.aspx
• http://www.mssqltips.com/tutorial.asp?tutorial=105