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 --
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.name AS backupset_name,
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)
MSSQLTips.com – Database Backups for all databases For Previous Week
AS a production DBA, you need to make sure that your backup and restoration process is working perfectly fine. Everything else is secondary.
The following query help identify which databases in the particular instance have no prior full backup taken. This query is practically usefully when there are hundred or thousand of databases hosted in a single instance. It not only help identifying newly created databases or newly migrated databases, it also help ensure the backup and restoration process is working properly, data is backed up, so that data can be recovered whenever it is needed.
Below is the script which help you to determine missing full backup for all your databases.
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'
bs.backup_set_id IS NULL
AND d.recovery_model_desc <> 'SIMPLE'
I recently read an article Finding Last Backup Time for All Database from SQLAuthority.com. I found this useful T-SQL scripts.
-- Get Backup History for required database
SELECT TOP 100
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
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
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.