Backup History and Header Information (SQL Server) • • 7 minutes to read • Contributors • • • In this article APPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse A complete history of all SQL Server backup and restore operations on a server instance is stored in the msdb database. This topic introduces the backup and restore history tables and also the Transact-SQL statements that are used to access backup history. The topic also discusses when listing database and transaction log files is useful and when to use media-header information compared to when to use backup-header information. Important To manage the risk of losing recent changes to your backup and restore history, back up msdb frequently. For information about which of the system databases you must back up, see.
In This Topic: • • • • • • • • Backup and Restore History Tables This section introduces the history tables that store backup and restore metadata in the msdb system database. History table Description Contains one row for each data or log file that is backed up. Contains a row for each filegroup in a backup set. Contains one row for each media family. If a media family resides in a mirrored media set, the family has a separate row for each mirror in the media set.
Contains one row for each backup media set. Contains a row for each backup set. Contains one row for each restored file. This includes files restored indirectly by filegroup name. Contains one row for each restored filegroup. Contains one row for each restore operation.
Norton Delete Backup Set And Files
You can use information in the backup header to identify which backup set to restore. The Database Engine numbers each backup set on the backup media. This lets you identify the backup set you want to restore by using its position on the media. Mar 23, 2012 Position tells you a backup’s position within the backup set, for when you’re storing multiple backups within a single file. DeviceType tells you what sort of backup device this backup was initially stored on. The next set of information is absolutely vital to tracking your backup information.
Important The RESTORE FILELISTONLY, RESTORE HEADERONLY, RESTORE LABELONLY, and RESTORE VERIFYONLY Transact-SQL statements require CREATE DATABASE permission. This requirement secures your backup files and protects your backup information more fully than in previous versions. Mac 800g in wisconsin. For information about this permission, see. Information statement Backup history table Description Returns a result set that has a list of the database and log files that are contained in the specified backup set.
Retrospect Bad Backup Set Header Found
For more information, see 'Listing Database and Transaction Log Files,' later in this topic. Retrieves all the backup header information for all backup sets on a particular backup device. The result from executing RESTORE HEADERONLY is a result set. For more information, see 'Viewing the Backup-Header Information,' later in this topic. Returns a result set that contains information about the backup media on a specified backup device.