Adding Notes to Backups

Compresses, encrypts, secures and monitors SQL Server backups.

Adding Notes to Backups

Postby calvis » Sun Jul 08, 2012 3: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
calvis
 
Posts: 1
Joined: Sun Jul 08, 2012 3:35 am

Postby petey » Mon Jul 09, 2012 2: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
petey
 
Posts: 2301
Joined: Sun Apr 24, 2005 12:34 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 0 guests

cron