Adding Notes to Backups

calvis

How do we add notes to our backups so we can be more informative on why a particular backup was made?

petey

You can do this using the DESCRIPTION option e.g.

EXEC master..sqlbackup '-sql "BACKUP DATABASE AdventureWorks TO DISK = [e:\backups\AdventureWorks.sqb] WITH DESCRIPTION = [Backup prior to applying patch #001]"'

To view the description, use the RESTORE HEADERONLY command e.g.

EXEC master..sqlbackup '-sql "RESTORE HEADERONLY FROM DISK = [e:\backups\AdventureWorks.sqb]"'

DESCRIPTION accepts up to 255 characters, and the value is also stored in the SQL Server backup history tables. E.g.

SELECT a.description 
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE b.physical_device_name = 'e:\backups\AdventureWorks.sqb'
  AND b.device_type = 7