How to get the backup history of databases in SQL Server

How to get the backup history of databases in SQL Server (T-SQL)

Introduction

This T-SQL script will demo how to get the backup history of databases in SQL Server.

Scenarios

As many people asked how to get the backup history of SQL Server databases, this script will provide some convenience.

Script

You can use this script in this way:
1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.
2. Copy the code from backuphistory.sql, paste it in a new query and run the script.

After the script finishes running, we’ll get the following figure:

Here are some code snippet for your reference:

SQL

Edit|Remove

SELECT bs.server_name AS ServerName,
       CASE bs.compatibility_level 
         WHEN 90 THEN 'SQL Server 2005'
         WHEN 100 THEN 'SQL Server 2008 or SQL Server 2008 R2' 
         WHEN 110 THEN 'SQL Server 2012' 
         WHEN 120 THEN 'SQL Server 2014'
       END AS ServerVersion, 
       bs.database_name AS DatabseName,
       CASE bs.type 
         WHEN 'D' THEN 'Full' 
         WHEN 'I' THEN 'Database Differential' 
         WHEN 'L' THEN 'Log' 
         WHEN 'F' THEN 'File or filegroup'
         WHEN 'G' THEN 'Differential file'
         WHEN 'P' THEN 'Partial'
         WHEN 'Q' THEN 'Differential partial' 
       END AS BackupType,
       bs.backup_start_date AS BackupStartDate,
       bs.backup_finish_date AS BackupFinishDate, 
       CASE bmf.device_type 
         WHEN 2 THEN 'Disk' 
         WHEN 5 THEN 'Tape'
         WHEN 7 THEN 'Virtual device'   
         WHEN 105 THEN 'A permanent backup device' 
         ELSE 'Other Device' 
       END AS DeviceType,
       bmf.physical_device_name AS PhysicalDevice,
       bs.backup_size/(1024*1024) AS [BackupSize(MB)], 
       bs.compressed_backup_size/(1024*1024) AS [ConmpressedBackupSize(MB)]

SELECTbs.server_nameASServerName, 
       CASEbs.compatibility_levelWHEN90THEN'SQL Server 2005'WHEN100THEN'SQL Server 2008 or SQL Server 2008 R2'WHEN110THEN'SQL Server 2012'WHEN120THEN'SQL Server 2014'ENDASServerVersion,  
       bs.database_nameASDatabseName, 
       CASEbs.typeWHEN'D'THEN'Full'WHEN'I'THEN'Database Differential'WHEN'L'THEN'Log'WHEN'F'THEN'File or filegroup'WHEN'G'THEN'Differential file'WHEN'P'THEN'Partial'WHEN'Q'THEN'Differential partial'ENDASBackupType, 
       bs.backup_start_dateASBackupStartDate, 
       bs.backup_finish_dateASBackupFinishDate,  
       CASEbmf.device_typeWHEN2THEN'Disk'WHEN5THEN'Tape'WHEN7THEN'Virtual device'WHEN105THEN'A permanent backup device'ELSE'Other Device'ENDASDeviceType, 
       bmf.physical_device_nameASPhysicalDevice, 
       bs.backup_size/(1024*1024) AS [BackupSize(MB)],  
       bs.compressed_backup_size/(1024*1024) AS [ConmpressedBackupSize(MB)]

Prerequisites

SQL Server 2008 or higher version