SQL Server DBA Tip : Check Your Last Backup Date With One Query

I’m using the below T-SQL query to determine the last backup date of SQL Server Database. Before we get started, you also learn how to get all table names and their row counts in SQL Database.


SELECT sdb.Name AS DatabaseName,COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb 
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

SELECT sdb.Name AS DatabaseName,COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime

FROM sys.sysdatabases sdb

LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name

GROUP BY sdb.Name

I’m using the below T-SQL query to determine the last backup date of database and also return the size and location of the backup file.


WITH LastBackUp AS
(
SELECT  bs.database_name,
        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(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB],
        backup_start_date AS [Last Full DB Backup Date],
        physical_device_name AS [Backup File Location]
FROM LastBackUp
WHERE Position = 1
ORDER BY [Database];