Have you ever wanted a quick way to check that all of your databases that are either in FULL or BULK_LOGGED recovery mode have transaction log backups setup? The script below is a nice safety net to ensure that all of your non-simple recovery mode databases have transaction log backups setup.
Bottom line… if you have a database that has it’s recovery mode set to BULK_LOGGED or FULL recovery mode you should be backing up your transaction log periodically.
Enjoy
DECLARE @num_of_days INT
SET @num_of_days = 1
CREATE TABLE #DatabaseListNotSimple (dbid INT IDENTITY(1,1), DatabaseName VARCHAR(50))
INSERT INTO #DatabaseListNotSimple
SELECT name AS ‘DatabaseName’
FROM MASTER.dbo.sysdatabases WITH(NOLOCK)
WHERE (CONVERT(VARCHAR(50),DATABASEPROPERTYEX(name,‘Recovery’))= ‘FULL’ OR
CONVERT(VARCHAR(50),DATABASEPROPERTYEX(name,‘Recovery’))= ‘BULK_LOGGED’) AND
name <> ‘model’
SELECT A.DatabaseName, MAX(B.backup_finish_date) AS ‘LastLogBackupDateTime’
FROM #DatabaseListNotSimple A LEFT OUTER JOIN
msdb.dbo.backupset B WITH(NOLOCK) ON A.DatabaseName = B.database_name
WHERE (B.TYPE = ‘L’ OR B.TYPE IS NULL)
GROUP BY A.DatabaseName
HAVING (MAX(B.backup_finish_date) < GETDATE() - @num_of_days OR
MAX(B.backup_finish_date) IS NULL)
DROP TABLE #DatabaseListNotSimple
GO
DROP TABLE #DatabaseListNotSimple
GO
