In SQL Server 6.5, tables have been added to the msdb database to aid in maintaining backup information. These are the new tables.
| Table name | Description |
|---|---|
| sysbackupdetail | Specifies a summary of the devices used to backup (dump). |
| sysbackuphistory | Specifies a summary of each backup operation (dump). |
| sysrestoredetail | Specifies a summary of the devices used to restore (load). |
| sysrestorehistory | Specifies a summary of each restore operation (load). |
SQL Server automatically maintains a complete online backup and restore history in the msdb database. This information includes who performed the backup, at what time, and on which devices or files it is stored.
Backup and restore events are recorded even if they were created with custom applications or third-party tools. A Visual Basic application that calls SQL-DMO objects to perform backup and restore operations will cause the events to be logged in these tables.
After each DUMP statement is executed, a description of the dump is saved in sysbackuphistory and the dump device details are saved in sysbackupdetail as part of a single transaction.
After each LOAD statement is executed, a description of the restore is saved in sysrestorehistory and the device details are saved in sysrestoredetail. If for any reason the history cannot be written to msdb database, a message is written to the Windows NT event log and to the SQL Server error log.