Adding Notes to Backups

Compresses, encrypts, secures and monitors SQL Server backups.

Adding Notes to Backups

Postby calvis » Sun Jul 08, 2012 2:39 am

How do we add notes to our backups so we can be more informative on why a particular backup was made?
Charles Alvis
Posts: 1
Joined: Sun Jul 08, 2012 2:35 am

Postby petey » Mon Jul 09, 2012 1:34 am

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

Code: Select all
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.

Code: Select all
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.

Code: Select all
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
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Posts: 2310
Joined: Sun Apr 24, 2005 11:34 am

Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 0 guests