Using msdb Backup Tables

I have two questions about backing up and restoring files. First, how can I determine when my last backup occurred? And second, how can I retrieve the files from the list in the backup folder and restore them automatically?

SQL Server maintains four tables that contain information about your backups:msdb.dbo.backupfile, msdb.dbo.backupmediafamily, msdb.dbo.backupmediaset, and msdb.dbo.backupset. Every time you perform a backup, SQL Server updates the information in these tables unless you call one of the following maintenance stored procedures:

EXEC msdb.dbo.sp_delete_ backuphistory @oldest_date
EXEC msdb.dbo.sp_delete_data base_backuphistory @db_nm
EXEC dbo.sp_delete_ backup_and_restore_history @database_name

These stored procedures remove the history information based on date or on a per database basis.

The diagram in Figure 1 shows the relationship between SQL Server's backup information tables and the counterparts that are maintained by RESTORE (msdb.dbo.restorefile, msdb.dbo.restorefilegroup, and msdb.dbo.restorehistory). You can use the information in the backup tables to answer many questions. For example, if you want to know when your database was last backed up, you can use the following query:

SELECT db.name AS \[Database\],
  max(backup_finish_date) 
  AS \[LastBackupDate\] 
FROM
  \[master\].\[dbo\].\[sysdatabases\] 
  db LEFT OUTER JOIN 
  \[msdb\].\[dbo\]
  .\[backupset\] bs 
ON         bs.database_name
  = db.name 
AND       bs.type = 'D' 
GROUP BY db.name 
ORDER BY db.name

You can also use the information in these tables to retrieve the file list you need for your restore operation. Although the technique is beyond the scope of this column, you can actually generate a restore script based on the information available, if you're really motivated. Examples of such restore scripts are available on the Internet. Alternatively, you can retrieve information from the backup files directly by using the RESTORE FILELISTONLY command, as you can see in the following code:

INSERT TABLE #filelist 
(
  \[LogicalName\]  sysname NOT NULL, 
  \[PhysicalName\] sysname NOT NULL, 
  \[Type\]        char(1) NOT NULL, 
  \[FileGroupName\]   sysname NULL,
  \[Size\]         bigint NOT NULL, 
  \[MaxSize\]   bigint NOT NULL 
)
INSERT INTO #filelist EXEC(
  'RESTORE FILELISTONLY FROM 
  DISK 
  = 'c:\testdb.bak') 
SELECT * FROM #filelist

Then, you can store the returned information in a temporary table and use it to build a RESTORE command. This approach is handy if you don't have the msdb database—for example, when you get a backup file from another machine.

—Gert E.R. Drapers
Development Manager
Visual Studio Team System
for Database Developer

Comments

Plain text