SQL Server Monitor: Database Backup Status and Best Practices

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

    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.

    © 2026 Crystal Enterprise, LLC. All rights reserved.