Presents your 
SQL SERVER E-NEWSLETTER for December 17, 2002
<------------------------------------------->

FIND DUMP DEVICE AND BACKUP INFORMATION IN THE MSDB DATABASE

Files recognized and managed by SQL Server that contain database backup
information are called .Dump devices. You can create the dump device and
then back up databases to it. You can also back up master, model, and
msdb to the same dump device. In addition, you can use SQL Server  Enterprise
Manager (SEM) to administer the devices and show information about what
was backed up, when it was backed up, and the size of the backup.

You can use the tables, backupset and backupmediaset, in the msdb
database to find the information on database backups as well. Backupmediaset
identifies the dump device where the table backupset categorizes the
database backups on the dump device. Even when the backup disk files are
deleted, the information is still retained in the tables. This is an excellent
source to find out the history on backups even when the backup files are
not present.

The following is a sample script illustrating the information being
tracked in backupmediaset and backupset in the msdb database.

Before running this script, make sure you have enough disk space to back
up the master, model, and msdb databases on the C: drive. Use this
script on a development server and not a production server. Be certain you
have backups available for the master, model, and msdb databases for
recoverability other than the C:\SystemDump.BAK file made in the following
script:

--**********************************************************
-- ADD DUMP DEVICE
--**********************************************************
exec sp_addumpdevice @devtype = 'disk',
                     @logicalname = 'SystemDump',
                     @physicalname = 'c:\SystemDump.BAK',
                     @cntrltype = 2 -- disk
GO
--**********************************************************
-- BACKUP SYSTEM DATABASES
--**********************************************************
BACKUP DATABASE master TO SystemDump WITH INIT
     ,MEDIANAME = 'SystemDump', NAME = 'master_backup'
BACKUP DATABASE model TO SystemDump WITH NOINIT
     ,MEDIANAME = 'SystemDump', NAME = 'model_backup'
BACKUP DATABASE msdb TO SystemDump WITH NOINIT
     ,MEDIANAME = 'SystemDump', NAME = 'msdb_backup'
GO
RESTORE LABELONLY FROM SystemDump
RESTORE HEADERONLY FROM SystemDump
RESTORE FILELISTONLY FROM SystemDump WITH FILE = 1
RESTORE FILELISTONLY FROM SystemDump WITH FILE = 2
RESTORE FILELISTONLY FROM SystemDump WITH FILE = 3
RESTORE VERIFYONLY FROM SystemDump WITH FILE = 1
RESTORE VERIFYONLY FROM SystemDump WITH FILE = 2
RESTORE VERIFYONLY FROM SystemDump WITH FILE = 3
GO
--**********************************************************
-- SELECT FROM BACKUPMEDIASET FOR DUMP DEVIICE
--**********************************************************
SELECT * FROM MSDB..BACKUPMEDIASET
WHERE name = 'SystemDump'
GO
--**********************************************************
-- SELECT FROM BACKUPSET FOR DATABASE BACKUUPS
--**********************************************************
SELECT * FROM MSDB..BACKUPSET
WHERE name in ('master_backup','model_backup','msdb_backup')
GO
--**********************************************************
-- BACKUP SYSTEM DATABASES A SECOND TIME
-- WITHOUT FORMATTING DUMP DEVICE
--**********************************************************
BACKUP DATABASE master TO SystemDump WITH NOINIT
     ,MEDIANAME = 'SystemDump', NAME = 'master_backup'
BACKUP DATABASE model TO SystemDump WITH NOINIT
     ,MEDIANAME = 'SystemDump', NAME = 'model_backup'
BACKUP DATABASE msdb TO SystemDump WITH NOINIT
     ,MEDIANAME = 'SystemDump', NAME = 'msdb_backup'
GO
--**********************************************************
-- FIND INFORMATION IN BACKUPMEDIASET AND BBACKUPSET
--**********************************************************
SELECT S.backup_set_id, S.position, S.backup_finish_date,
     S.name FROM MSDB..BACKUPMEDIASET MS
JOIN MSDB..BACKUPSET S ON S.media_set_id = MS.media_set_id
WHERE MS.name = 'SystemDump'
GO
--**********************************************************
-- DROP DUMP DEVICE WITHOUT DELETING DISK FFILE
--**********************************************************
EXEC sp_dropdevice @logicalname = 'SystemDump'
     --, @delfile = 'DELFILE'
GO
--**********************************************************
-- READING DUMP DEVICE - YOU DON'T LOSE INFFORMATION!!
--**********************************************************
exec sp_addumpdevice @devtype = 'disk',
                     @logicalname = 'SystemDump',
                     @physicalname = 'c:\SystemDump.BAK',
                     @cntrltype = 2
GO
--**********************************************************
-- VERIFYING INFORMATION IS NOT LOST
--**********************************************************
SELECT S.backup_set_id, S.position, S.backup_finish_date,
     S.name FROM MSDB..BACKUPMEDIASET MS
JOIN MSDB..BACKUPSET S ON S.media_set_id = MS.media_set_id
WHERE MS.name = 'SystemDump'
GO

----------------------------------------