Generate Full Backup Scripts for Databases Missing Today's Backup

 /**********************************************************************************

TSQL Script: Generate Backup Scripts for Databases Without Today's Full Backup

 --#Description:

 This script identifies all user databases in the SQL Server instance that have not had a 

 full backup on the current day. It then generates a full `BACKUP DATABASE` command 

 for each of those databases, saving the backup to a specified folder.

 --#Usage Notes:

 - Review and update the target backup path (`G:\MSSQL15\MSSQL\Backup\FULL\`) 

   based on your environment.

 - This script targets only FULL backups (`type = 'D'`) and excludes `tempdb`.

 - Useful for daily health checks, automation, or pre-maintenance validation.

***********************************************************************************/

-- Declare today's date for comparison

DECLARE @Today DATE = CAST(GETDATE() AS DATE);

-- Table to store databases needing backup

DECLARE @BackupList TABLE (DatabaseName SYSNAME, LastBackup DATETIME);

-- Find user databases without today's full backup

INSERT INTO @BackupList (DatabaseName, LastBackup)

SELECT d.name, MAX(b.backup_finish_date) AS LastFullBackupDate

FROM sys.databases d

LEFT JOIN msdb.dbo.backupset b 

    ON b.database_name = d.name 

    AND b.type = 'D'  -- 'D' = Full backup

WHERE d.name <> 'tempdb'

  AND d.state_desc = 'ONLINE'

  AND d.is_read_only = 0

GROUP BY d.name

HAVING MAX(b.backup_finish_date) IS NULL 

    OR CAST(MAX(b.backup_finish_date) AS DATE) < @Today;

-- Generate full backup scripts for missing databases

SELECT 

    'BACKUP DATABASE [' + DatabaseName + '] TO DISK = ''G:\MSSQL15.FERACK4\MSSQL\Backup\FULL\' 

    + DatabaseName + '_FULL_' + CONVERT(VARCHAR, @Today, 112) + '.bak'' 

    WITH INIT, FORMAT, NAME = ''Full Backup of ' + DatabaseName + ''', 

    COMPRESSION, STATS = 1;' 

    AS BackupScript

FROM @BackupList;