Databases Missing a Data (aka Full) Back-Up Within Past 24 Hours

--Databases with data backup over 24 hours old
SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
   DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
FROM    msdb.dbo.backupset
WHERE     msdb.dbo.backupset.type = 'D' 
GROUP BY msdb.dbo.backupset.database_name
HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE())) 

UNION 

--Databases without any backup history
SELECT     
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   master.dbo.sysdatabases.NAME AS database_name, 
   NULL AS [Last Data Backup Date], 
   9999 AS [Backup Age (Hours)] 
FROM
   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name
WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb'
ORDER BY 
   msdb.dbo.backupset.database_name






http://www.mssqltips.com/tip.asp?tip=1601



CREATE PROCEDURE sp_generateDifferentialBackups 
   @strBackupPath NVARCHAR (25) ='D:\backups\'--variable for location of DIFFERENTIAL backups 
AS 
SET NOCOUNT ON  
-- Get the name of all user databases   
DECLARE @strTimeStamp NVARCHAR(12)   --variable for timestamp value  
DECLARE @strSQL NVARCHAR(100)        -- used for generating dynamic SQL statements  
DECLARE @databaseName NVARCHAR(128)  -- used as variable to store database names  
DECLARE dbCursor CURSOR FOR       -- used for cursor allocation  
   SELECT NAME 
   FROM MASTER.SYS.DATABASES 
   WHERE [database_id] > 0 
   AND NAME NOT IN ('MASTER','MODEL','MSDB','TEMPDB') --does not include the system databases  

OPEN dbCursor  
  
FETCH NEXT FROM dbCursor INTO @databaseName  
  
WHILE (@@FETCH_STATUS = 0)  
BEGIN  
   PRINT 'Checking for the latest FULL database backup for: [' + @databaseName +']' 
      
   DECLARE @strphysical_device_name NVARCHAR(100) -- variable for physical_device_name  
   DECLARE @cursor NVARCHAR(400)  

   -- Reads the MSDB database to check for the latest FULL database backup        
   SELECT @cursor=('DECLARE TabCursor CURSOR FAST_FORWARD GLOBAL FOR ' +  
   'SELECT TOP 1 physical_device_name  
   FROM msdb.dbo.backupset a INNER JOIN msdb.dbo.backupmediafamily b     
   ON a.media_set_id = b.media_set_id     
   WHERE database_name=''' + @databaseName + '''  
   AND type=''D''  
   ORDER BY backup_finish_date desc')  
   EXEC sp_executesql @cursor  
   OPEN TabCursor  
  
   FETCH NEXT FROM TabCursor INTO @strphysical_device_name  
   --Check if the database does not have any FULL backups at all 
   IF @@FETCH_STATUS <> 0 
   BEGIN 
      PRINT '*****WARNING: Database [' + @databaseName + '] does not have any FULL database backups at all. Generate the FULL database backup first. Aborting DIFFERENTIAL backup command.*****' 
   END 
   ELSE    
   --If the database has FULL backups, 
   WHILE @@FETCH_STATUS = 0  
   BEGIN  
      DECLARE @result INT  
      EXEC xp_fileexist @strphysical_device_name, @result output   
      IF (@result = 1)  --@result will return 1 if the specified file exists, if it doesn't, it will return 0  
      BEGIN  
         SET @strTimeStamp=CONVERT(CHAR(8), GETDATE(), 112)  
         SET @strTimeStamp=@strTimeStamp + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')  
         EXEC ('BACKUP DATABASE ' + @databaseName + ' TO DISK=''' + @strBackupPath + @databaseName + '_DIFF_' + @strTimeStamp + '.BAK'' WITH INIT, DIFFERENTIAL, DESCRIPTION=''DIFFERENTIAL Backup for ' + @databasename + ' database''') -- change this value should you decide to change the backup type to something other than differential   
         PRINT '==========================================================================================='  
         PRINT 'DIFFERENTIAL Backup generated for database: [' + @databaseName  + ']' 
         PRINT 'Corresponding FULL database backup: ' + @strphysical_device_name  
         PRINT 'DIFFERENTIAL database backup: ' + @strBackupPath + @databaseName + '_DIFF_' + @strTimeStamp + '.BAK'  
      END  
      ELSE  
         PRINT  '*********WARNING: FULL database backup file is missing. Generate the FULL database backup first. Aborting DIFFERENTIAL backup command.*********'  
         
      FETCH NEXT FROM TabCursor INTO @strphysical_device_name  
   END  
   CLOSE TabCursor  
   DEALLOCATE TabCursor  
       
   PRINT '==========================================================================================='  
   PRINT '                                                                                           '  
   PRINT '                                                                                           '  
   PRINT '                                                                                           '  
   
   FETCH NEXT FROM dbCursor INTO @databaseName  
END  
  
CLOSE dbCursor  
DEALLOCATE dbCursor  

PRINT 'FINISHED'  
GO