SQL Server Management Studio Standard Reports – Backup and Restore Events: [DatabaseName]

I'm continuing my series on the Standard Reports in SQL Server Management Studio, and today we’re in the database reports. You can find these reports by starting SQL Server Management Studio and right-clicking a database name. From the menu that appears, click the “Standard Reports”, and then select the title at the top of this post.

If you kick off a backup or restore operation, this report will track the event and also provide information like the size and location for the operations. Note that this reports is per database, so each database will have its own event history. If the database has both backup and restore opertions performed on it, they will both be in the same report.

A very useful feature in this report is that you will get an average on the Backup time for the database. This is important so that you can plan your maintenance windows. Here are the additional columns you’ll get in this report.

The first band shows the Average Time Taken For Backup Operations:

Column	Description
Backup Type	The backup type that was taken, such as Full, Differential and so on.
Average Duration (min.)	The average length of time, in minutes, the backups for this database have taken.

The second band shows the Average Successful Backup Operations:

Column	Description
Start Time	The time this particular database run started
Duration (minutes)	The length of time in minutes that this backup run took
Backup Type	The type of backup (Full, Differential, etc.) for this run
Backup Size	The size this backup takes on the on storage media
Backup Name	The name of the backup
Device Type	The type of device where the backup was taken (disk, tape, etc.)
User Name	The name of the account that performed the backup – if automated, the name the service account uses
Recovery Model	The type of recovery model (Simple, Full, Bulk-Logged)
Differential Base LSN	A Log Sequence Number used for differential backups. Data extents changed after this LSN will be included in a differential backup.
Last LSN	The Log sequence number of the next log record after the backup set. The last LSN identifies the next log record beyond the end of the backup. For data and differential backups (and for log backups containing bulk-logged operations), roll forward must go at least up to this LSN. Otherwise, the data copied during restore is inconsistent. For log backups, the log backup includes log records up to but not including this LSN.
Physical Device Name	The device file name (such as the Operating System File Name) where the backup was stored.

The third band shows the Backup Operation Errors. Note that some of this information requires the default trace to be running:

Column	Description
Date-Time	The date and time that the backup error was required
Error ID	The ID number of the error
Severity	The severity number associated with this instance of the error
Error Message	The error message text
Application Name	The name of the application that reported the error
Login Name	The login that reported the error
Host Name	The name of the system that reported the error

The final band shows the Successful Restore Operations:

Column	Description
Start Time	The time the restore operation started
Destination	The database the backup was restored to
Restore Type	The type of restoration, such as database, log or filegroup
Mode	The replace operation type
Recovery Option	The recovery option specified, such as “recovery” or “no recovery”
User	The login account that started the restore operation
Backup Name	The name of the backup that was restored
Backup Date	The date the backup was taken
Backup Type	The type of backup that was taken
Backup First LSN	The “begin of checkpoint” that is triggered when the backup starts. This LSN will coincide with first_lsn if the backup is taken when the database is idle and no replication is configured.
Backup Last LSN	The “stop of checkpoint” that is triggered when the backup ends.
Destination Physical Files	The location and filenames that the restore files were placed on disk. Note that there will be one or more files for the database and one or more files for the logs