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:
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:
(
\[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. DrapersDevelopment Manager
Visual Studio Team System
for Database Developer
