Essential Scripts related to SQL Server Database Backups

Introduction

Database backups are crucial for ensuring data integrity and disaster recovery. As a database administrator or developer, having reliable scripts to monitor and manage backups is essential. In this blog post, we’ll explore a collection of scripts that will help you efficiently track backup details, history, and analyze space requirements. Let’s dive in!

Script to find Backup Detail including Backup path, most recent backup date
Script to find Full Database Backup history
Script to find Log Backup history
Script to Backup all User Databases
Script to find Missing Backup
Script to analyze space required for database if backups are failing due to space issue
Script to Get Backup and Restore Status

Script to find Backup Detail including Backup path, most recent backup date

DECLARE @TYPE CHAR(1)
SET @TYPE = 'D' -- FULL Backup 'D', 'L' for Log Backup

SELECT
    ServerName = @@servername,
    Edition = Serverproperty('Edition'),
    ProductLevel = Serverproperty('ProductLevel'),
    ServerVersion = Serverproperty('Productversion'),
    DatabaseName = sd.name,
    DBStatus = Databasepropertyex(sd.name, 'Status'),
    RecoveryModel = Databasepropertyex(sd.name, 'Recovery'),
    IsBackupDone = CASE WHEN Bkp.database_name IS NULL THEN 'No' ELSE 'Yes' END,
    Bkp.physical_device_name,
    Bkp.backup_finish_date
FROM
    sysdatabases sd
    LEFT JOIN (
        SELECT
            x.database_name,
            z.physical_device_name,
            x.backup_finish_date
        FROM
            msdb.dbo.backupset x
            JOIN (
                SELECT
                    a.database_name,
                    MAX(a.backup_finish_date) backup_finish_date
                FROM
                    msdb.dbo.backupset a
                WHERE
                    type = @TYPE
                GROUP BY
                    a.database_name
            ) y ON x.database_name = y.database_name AND x.backup_finish_date = y.backup_finish_date
            JOIN msdb.dbo.backupmediafamily z ON x.media_set_id = z.media_set_id
    ) BKP ON sd.name = Bkp.database_name
WHERE
    sd.name <> 'tempdb'

SQL

Script to find Full Database Backup history

SELECT
    x.database_name,
    z.physical_device_name,
    CONVERT(char(20), x.backup_finish_date, 108) FinishTime,
    x.backup_finish_date
FROM
    msdb.dbo.backupset x
    JOIN (
        SELECT
            a.database_name,
            MAX(a.backup_finish_date) backup_finish_date
        FROM
            msdb.dbo.backupset a
        WHERE
            type = 'D'
        GROUP BY
            a.database_name
    ) y ON x.database_name = y.database_name
    AND x.backup_finish_date = y.backup_finish_date
    JOIN msdb.dbo.backupmediafamily z ON x.media_set_id = z.media_set_id

SQL

Script to find Log Backup history

SELECT
    x.database_name,
    z.physical_device_name,
    CONVERT(char(20), x.backup_finish_date, 108) FinishTime,
    x.backup_finish_date
FROM
    msdb.dbo.backupset x
    JOIN (
        SELECT
            a.database_name,
            MAX(a.backup_finish_date) backup_finish_date
        FROM
            msdb.dbo.backupset a
        WHERE
            type = 'L'
        GROUP BY
            a.database_name
    ) y ON x.database_name = y.database_name
    AND x.backup_finish_date = y.backup_finish_date
    JOIN msdb.dbo.backupmediafamily z ON x.media_set_id = z.media_set_id

SQL

Script to Backup all User Databases

DECLARE @DatabaseName sysname
DECLARE @BackupPath nvarchar(500)
DECLARE @SQL nvarchar(max)

-- Set the backup path
SET @BackupPath = 'C:\SQL_DATA\Backup\' -- Replace with your desired backup path

-- Create a cursor to loop through all user databases
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE database_id > 4 -- Exclude system databases

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Generate the dynamic SQL statement for each database
    DECLARE @BackupFileName nvarchar(500)
    SET @BackupFileName = @BackupPath + @DatabaseName + '_' + REPLACE(CONVERT(nvarchar(16), GETDATE(), 120), ':', '_') + '.bak'
    SET @SQL = 'BACKUP DATABASE ' + QUOTENAME(@DatabaseName) + ' TO DISK = ''' + @BackupFileName + ''' WITH COMPRESSION'

    -- Execute the backup command
    EXEC sp_executesql @SQL

    FETCH NEXT FROM db_cursor INTO @DatabaseName
END

CLOSE db_cursor
DEALLOCATE db_cursor

SQL

Script to find Missing Backup

DECLARE @threshold INT

SET @threshold = 32 -- Hours

SELECT
    @@servername AS InstanceName,
    d.name AS Database_Name,
    'Backup did not occur within ' + CAST(@threshold AS VARCHAR) + ' hours.' AS Message,
    a.backup_start_date AS Last_Backup,
    a.backup_size,
    CASE b.device_type
        WHEN 2 THEN 'Disk - Temporary'
        WHEN 102 THEN 'Disk - Permanent'
        WHEN 5 THEN 'Tape - Temporary'
        WHEN 105 THEN 'Tape - Permanent'
        WHEN 7 THEN 'Network'
        ELSE 'Unknown'
    END AS Device_Type,
    b.physical_device_name AS Physical_Path
FROM (
    SELECT
        a.media_set_id,
        a.database_name,
        a.backup_start_date,
        a.backup_size,
        b.type
    FROM
        msdb.dbo.backupset a
        JOIN (
            SELECT
                database_name,
                MAX(backup_finish_date) AS backup_finish_date,
                type
            FROM
                msdb.dbo.backupset
            WHERE
                type = 'D'
            GROUP BY
                database_name,
                type
        ) b ON a.database_name = b.database_name
        AND a.backup_finish_date = b.backup_finish_date
) a
JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
RIGHT JOIN master..sysdatabases d ON a.database_name = d.name
WHERE
    d.name <> 'tempdb'
    AND (DATEDIFF(hh, a.backup_start_date, GETDATE()) > @threshold OR a.backup_start_date IS NULL)
ORDER BY d.name

SQL

Script to analyze space required for database if backups are failing due to space issue

SELECT
    SUM(CAST((a.size * 8.00) / 1024 AS NUMERIC(12, 2))) AS DB_Size_in_MB
FROM
    sysaltfiles a
    JOIN sysdatabases b ON a.dbid = b.dbid
WHERE
    DATABASEPROPERTYEX(b.name, 'status') = 'ONLINE'
    AND b.name IN (
        SELECT
            a.name
        FROM
            master..sysdatabases a
            LEFT JOIN (
                SELECT
                    database_name,
                    MAX(backup_finish_date) AS backup_finish_date
                FROM
                    msdb..backupset
                WHERE
                    Type = 'D'
                GROUP BY
                    database_name
            ) b ON a.name = b.database_name
        WHERE
            (DATEDIFF(hh, b.backup_finish_date, GETDATE()) > 10 OR b.database_name IS NULL)
            AND (
                DATABASEPROPERTYEX(a.name, 'Status') = 'ONLINE'
                AND a.Name NOT LIKE '%temp%'
                AND a.Name NOT LIKE '%tmp%'
                AND a.Name NOT LIKE '%test%'
                AND a.Name NOT LIKE '%train%'
                AND a.Name NOT LIKE '%LiteSpeedLocal%'
            )
    )
GROUP BY
    b.name

SQL

Script to Get Backup and Restore Status

SELECT
    r.command,
    s.text,
    start_time,
    percent_complete,
    CAST(((DATEDIFF(s, start_time, GETDATE())) / 3600) AS VARCHAR) + ' hour(s), ' +
        CAST((DATEDIFF(s, start_time, GETDATE()) % 3600) / 60 AS VARCHAR) + ' min, ' +
        CAST((DATEDIFF(s, start_time, GETDATE()) % 60) AS VARCHAR) + ' sec' AS running_time,
    CAST((estimated_completion_time / 3600000) AS VARCHAR) + ' hour(s), ' +
        CAST((estimated_completion_time % 3600000) / 60000 AS VARCHAR) + ' min, ' +
        CAST((estimated_completion_time % 60000) / 1000 AS VARCHAR) + ' sec' AS est_time_to_go,
    DATEADD(second, estimated_completion_time / 1000, GETDATE()) AS est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command IN ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

SQL