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 Backup, Archive, and Restore user interface to select and restore those images.
Figure: Sample output of a backup set shows a sample output of a backup set.
To view backup information using the Microsoft SQL Server Management Studio.
- Open the Microsoft SQL Server Management Studio.
- Click New Query.
- 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
- Click Execute.