How to: Retrieve Database Backup History for All Databases for Previous Week

The [msdb] system database is the primary repository for storage of SQL Agent, backup, Service Broker, Database Mail, Log Shipping, restore, and maintenance plan metadata. Below are the handful of system views associated with database backups for this tip:

  • dbo.backupset : provides information concerning the most-granular details of the backup process
  • dbo.backupmediafamily :  provides metadata for the physical backup files as they relate to backup sets
  • dbo.backupfile :  this system view provides the most-granular information for the physical backup files

Based upon these tables, we can create a variety of queries to collect a detailed insight into the status of backups for the databases in any given SQL Server instance.

----------------------------------------------------------
-- Database Backups for all databases For Previous Week --
---------------------------------------------------------- 
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, 
   msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  
ORDER BY  
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_finish_date

Reference :

MSSQLTips.com – Database Backups for all databases For Previous Week

How to: Find Databases Without Full Backups

AS a production DBA, you need to make sure that your backup and restoration process is working perfectly fine. Everything else is secondary.

Below is the script which help you to determine missing full backup for all your databases.

SELECT
	d.name
FROM
	sys.databases d
	LEFT OUTER JOIN msdb.dbo.backupset bs
	ON d.name = bs.database_name
	AND bs.backup_finish_date > d.create_date
	AND bs.type = 'D'
WHERE
	bs.backup_set_id IS NULL
	AND d.recovery_model_desc <> 'SIMPLE'

How to: Get Database Backup History For A Single Database

I recently read an article Finding Last Backup Time for All Database from SQLAuthority.com. I found this useful T-SQL scripts.

USE AdventureWorks
GO
-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

It is a very neat script. The picture below show the backup history for single database adventureworks only. The same can be ran for multiple database as well if you just remove the WHERE condition.