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

Comments (2)

Cancel reply

  1. I'm continuing my series on the Standard Reports in SQL Server Management Studio , and today we’re

  2. Rajeev Ranjan says:

    I am using compress backup. However, the size of .bak file is different than the the backup size showing in the report.

    Can anyone explain?

Skip to main content