Question: How do you find the last full backup time and size for each database? Read the retained history in msdb.
During a Comprehensive Database Performance Health Check, an absent backup plan worries me more than a slow query. I’ve seen teams jump into action when we discover that gap. I’ve also seen it remain unresolved after we discuss the risk.
This query gives us a starting point: the latest recorded full backup for each current database. It keeps databases without matching history in the results, with null backup fields. It excludes tempdb, which isn’t backed up.
WITH RankedBackups AS
(
SELECT database_name, backup_finish_date, backup_size,
compressed_backup_size, is_copy_only,
ROW_NUMBER() OVER
(
PARTITION BY database_name
ORDER BY backup_finish_date DESC, backup_set_id DESC
) AS rn
FROM msdb.dbo.backupset
WHERE type = 'D' AND backup_finish_date IS NOT NULL
)
SELECT CONVERT(nvarchar(128), SERVERPROPERTY(N'ServerName')) AS server_name,
d.name AS database_name,
b.backup_finish_date,
CAST(b.backup_size / 1048576.0 AS decimal(20,2)) AS backup_MiB,
CAST(b.compressed_backup_size / 1048576.0 AS decimal(20,2)) AS stored_MiB,
b.is_copy_only
FROM sys.databases AS d
LEFT JOIN RankedBackups AS b ON b.database_name = d.name AND b.rn = 1
WHERE d.database_id <> 2
ORDER BY d.name;The ranking selects one completed full backup per database name. Sorting by database name alone would list history without identifying the latest backup. Joining backupmediafamily also adds rows for each media family, which isn’t needed for this question.
backup_MiB reports the backup’s uncompressed size. stored_MiB reports the stored backup size, including compression when used. These are backup metadata values, not a measurement of every byte in a multi-backup media file.
What this check proves
A null date means this instance’s retained history has no matching completed full backup. It doesn’t prove the database has never been backed up. History can be purged, and an availability group backup can run on another replica.
The name match also needs care after renames, restores or name reuse. Review the backup identity and location when investigating a particular database. The is_copy_only flag helps distinguish an ad hoc copy-only full backup from a conventional full backup.
I’d be happy to find a recent date, but I wouldn’t stop there. Confirm the files are available and test restoration. A successful backup record cannot tell us whether today’s recovery procedure will work.
What do you use to monitor backup freshness? Share the script, and how you check restores, in the comments.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
5 Comments. Leave new
If looking for the last backup would it be better to only get the last backup date using a CTE like this:
With MostRecent
As (Select
msdb.dbo.backupset.database_name,
Max(msdb.dbo.backupset.backup_finish_date) As max_backup_finish_date
From
msdb.dbo.backupset
Group By
msdb.dbo.backupset.database_name)
Select
Convert(char(100),Serverproperty(‘Servername’)) As Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date,
Cast(msdb.dbo.backupset.backup_size As numeric(35,2)) / 1048576.0 As backup_size_MB
From
msdb.dbo.backupmediafamily
Inner Join msdb.dbo.backupset
On msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
Join MostRecent As mr
On
mr.database_name = backupset.database_name And
mr.max_backup_finish_date = backupset.backup_finish_date
Where msdb.dbo.backupset.type = ‘D’
Order By
msdb.dbo.backupset.database_name;
Where we need to run this query?
This query returns one row for each file in the backup set, for multiple backups of the same database. If what is wanted is one row for the most recent full backup of each database, that can be obtained using the following query adapted from https://dba.stackexchange.com/questions/150241/t-sql-query-for-date-of-last-full-backup-size-and-location :
WITH LastBackUp AS
(
SELECT bs.database_name,
bs.compressed_backup_size,
bs.backup_size,
bs.backup_start_date,
bmf.physical_device_name,
Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
FROM msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
WHERE bs.[type] = ‘D’
AND bs.is_copy_only = 0
)
SELECT
database_name AS [Database],
CAST(compressed_backup_size / 1073741824 AS DECIMAL(10, 4)) AS [CompressedBackupSizeGB],
CAST(backup_size / 1073741824 AS DECIMAL(10, 4)) AS [BackupSizeGB],
backup_start_date AS [Last Full DB Backup Date],
physical_device_name AS [Backup File Location]
FROM LastBackUp
WHERE Position = 1
ORDER BY [Database];
how about if we want to know the last full back up only?
Hi, I get actual or physical backup file size .bak is different from this value size from query result,
I used compressed backup.
how to get size physical backup size actual from query.
thanks