Adding Notes to Backups

calviscalvis Posts: 2
edited July 8, 2012 9:34PM in SQL Backup Previous Versions
How do we add notes to our backups so we can be more informative on why a particular backup was made?
Charles Alvis

Comments

  • peteypetey Posts: 2,358 New member
    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
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.