SQL Server Monitor: Database Backup Status and Best Practices
Mar 02, 2024Simon Dang

SQL Server Database Backups
Backing up your SQL Server databases is a critical task to ensure the safety and integrity of your data. However, with various backup options and strategies available, it can be overwhelming to determine the best practices for your specific needs. In this post, we will demystify SQL Server backups and provide you with essential tips and strategies to help you streamline your backup process.
Query to List Last Backup Date for Every Database in a Server
DECLARE @space TABLE (
DatabaseID int
, DataSize float
, [DataUsedPct] float
, LogSize float
, [LogUsedPct] float)
DECLARE @sql nvarchar(2560)
DECLARE @id int
WHILE ISNULL(@id, 1) <= (
SELECT MAX(database_id) FROM sys.databases
)
BEGIN
IF EXISTS (
SELECT 1 FROM sys.databases
WHERE database_id = ISNULL(@id, 1)
AND has_dbaccess(name) = 1
AND state_desc = 'ONLINE'
)
BEGIN
SET @sql = N'USE [' + (
SELECT name FROM sys.databases
WHERE database_id = ISNULL(@id, 1)
) + '];
SELECT ' + CONVERT(nvarchar, ISNULL(@id, 1)) + '
, totalsizedata = SUM(
CASE WHEN type = 0
THEN size/128 ELSE 0 END)
, totalsizedatausedpct = CONVERT(decimal(4,1),
SUM(CASE WHEN type = 0
THEN spaceused ELSE 0 END)
* 100.0
/ SUM(CASE WHEN type = 0
THEN size ELSE 0 END))
, totalsizelog = SUM(
CASE WHEN type = 1
THEN size/128 ELSE 0 END)
, totalsizelogusedpct = CONVERT(decimal(4,1),
SUM(CASE WHEN type = 1
THEN spaceused ELSE 0 END)
* 100.0
/ SUM(CASE WHEN type = 1
THEN size ELSE 0 END))
FROM (
SELECT spaceused = FILEPROPERTY(name, ''spaceused''), *
FROM sys.database_files
) a1'
INSERT INTO @space
EXEC (@sql)
END
SET @id = ISNULL(@id, 1) + 1
END
SELECT [DatabaseID] = d.database_id
, [Database] = d.[Name]
, [Status] = d.state_desc
, [CreateDate] = CONVERT(char(10), d.create_date, 101)
+ RIGHT(CONVERT(char(19), d.create_date, 100), 8)
, [Recovery] = d.Recovery_Model_Desc
, [LastFullBackup] = (
SELECT CONVERT(char(10), MAX(backup_finish_date), 101)
+ RIGHT(CONVERT(char(19),
MAX(backup_finish_date), 100), 8)
FROM msdb.dbo.backupset
WHERE database_name = d.name AND type = 'D'
)
, [LastDiffBackup] = (
SELECT CONVERT(char(10), MAX(backup_finish_date), 101)
+ RIGHT(CONVERT(char(19),
MAX(backup_finish_date), 100), 8)
FROM msdb.dbo.backupset
WHERE database_name = d.name AND type = 'I'
)
, [LastLogBackup] = (
SELECT CONVERT(char(10), MAX(backup_finish_date), 101)
+ RIGHT(CONVERT(char(19),
MAX(backup_finish_date), 100), 8)
FROM msdb.dbo.backupset
WHERE database_name = d.name AND type = 'L'
)
, [DataSize] = CASE
WHEN st.Datasize/1024/1024 >= 1
THEN CONVERT(varchar,
CONVERT(decimal(8,1), st.DataSize/1024/1024.0))
+ ' TB'
WHEN st.Datasize/1024 >= 1
THEN CONVERT(varchar,
CONVERT(decimal(8,1), st.DataSize/1024.0))
+ ' GB'
ELSE CAST(st.DataSize AS varchar) + ' MB'
END
, [DataUsedPct]
, [LogSize] = CASE
WHEN st.Logsize/1024/1024 >= 1
THEN CONVERT(varchar,
CONVERT(decimal(8,1), st.Logsize/1024/1024.0))
+ ' TB'
WHEN st.Logsize/1024 >= 1
THEN CONVERT(varchar,
CONVERT(decimal(8,1), st.Logsize/1024.0))
+ ' GB'
ELSE CAST(st.Logsize AS varchar) + ' MB'
END
, [LogUsedPct]
, [Compatibility] = d.compatibility_level
FROM sys.databases d
LEFT JOIN @space st ON d.database_id = st.DatabaseID
ORDER BY d.[Name]
Monitor Backup Health with SQL Brainbox
Never miss a failed backup again. SQL Brainbox is a free SQL Server monitor that tracks backup status across all your databases and sends automated alerts when backups fail or fall behind schedule. Download SQL Brainbox to keep your SQL Server backup monitoring on track.