Viewing backup data using the Microsoft SQL Server Management Studio

By using the Microsoft SQL Server Management Studio and a specific query, you can view various SQL backup image information from the output of the query. The output contains database names, the backup start and finish date, and the backup type (FULL, CUMULATIVE, and DIFFERENTIAL). This query enables you to easily determine the backups for a database and then use the BAR user interface to select and restore those images.

To view backup information using the Microsoft SQL Server Management Studio.

  1. Open the Microsoft SQL Server Management Studio.

  2. Click New Query.

  3. Enter the following query

    Select

      CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

      msdb.dbo.backupset.database_name,

      msdb.dbo.backupset.backup_start_date,

      msdb.dbo.backupset.backup_finish_date,

      CASE msdb..backupset.type

    	WHEN 'D' THEN 'Database'

    	WHEN 'L' THEN 'Log'

    	WHEN 'I' THEN 'Differential'

      END AS backup_type

    FROM  msdb.dbo.backupmediafamily

      INNER JOIN msdb.dbo.backupset ON
msdb.dbo.backupmediafamily.media_set_id =
msdb.dbo.backupset.media_set_id

    ORDER BY

      msdb.dbo.backupset.database_name,

      msdb.dbo.backupset.backup_start_date

  4. Click Execute.