Getting database backup history in SQL Server

There are several ways to get the database backup history in SQL Server. Here I have listed two of the quickest ways to get the backup history. I use these methods pretty regularly. These methods will be helpful to confirm whether the latest backup was successfully taken before doing a major change in the database.

Using backup and restore events report

If you are using SQL Server Management Studio (SSMS) as your SQL Server client application, then the easiest way to get the database backup history and the latest backup details is to use the backup and restore events report. To launch this report

1. In SSMS object explorer panel, right-click the database.
2. From the right-click menu select Reports >> Standard Reports >> Backup and Restore Events.
   
3. In the report, you can expand the Successful Backup Operations section, to see the backup history.
   

Using T-SQL statement

If you are using some light weight SQL Server client application like Azure Data Studio, then this T-SQL statement will come in handy to get the backup history. The given statement will list the backup history of all the databases in the SQL Server. If need you can add a where clause to filter the result for a specific database using the column backupset.database_name.

SELECT 
	bs.database_name,
	bs.backup_start_date,
	bs.backup_finish_date,
	bs.server_name, 
	bs.user_name,
	bs.type,
	bm.physical_device_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bm on bs.media_set_id = bm.media_set_id

Reference

• More about the system table backupset at Microsoft Docs.