One of the perennial problems facing both experienced and involuntary DBAs is how to tell whether a database is really in the Full recovery model or not? This is complicated by the fact that when you switch a database into the Full recovery mode, it actually behaves as if it’s in the Simple recovery mode until the log backup chain is established (this is commonly called being in ‘pseudo-Simple’).<\/p>\n
It’s a problem for several reasons:<\/p>\n
1) if the database is really in the Full recovery model then log backups must be taken so the log can clear\/truncate properly and it doesn’t grow out of control<\/p>\n
2) if the database is in the Full recovery model but the log backup chain has been broken (or not established at all since the database was created) then log backups are not possible (except for the yuckiness in SQL 2000 when log backups would succeed without complaint but be totally useless during disaster recovery)<\/p>\n
I don’t know of any script to easily determine whether a database is really in the Full recovery mode, so I knocked one together – and I present it here for you to use.<\/p>\n
The trick to the script is finding the last LSN that’s been backed up for the database. if this is non-NULL, then a log backup chain exists and the database is really in the Full recovery mode. This is stored in the dbi_dbbackupLSN<\/em> field in the database boot page (see Search Engine Q&A #20: Boot pages, and boot page corruption<\/a>) but also nicely available in the DMV sys.database_recovery_status<\/em>.<\/p>\n I’ve tested this on 2005 SP3 and 2008 SP1.<\/p>\n Note that this doesn’t work on SQL 2000 – I’ve poked around and can’t find a way to get at the LSN without reading the boot page directly, which can’t be done gracefully inside a function – I’ll leave that as an exercise for you. You’d expect the IsTruncLog<\/em> property returned by DATABASEPROPERTY<\/em> to be correct when the database is in pseudo-Simple, but it’s not unfortunately.<\/p>\n Here are some test cases for the script:<\/p>\n This makes sense – the new FullModeDB<\/em> database is still in pseudo-Simple. Now what if we take a full database backup?<\/p>\n Perfect. Now what about switching it back to Simple and back to Full again?<\/p>\n Just as we expect – the log backup chain has been broken and the database is back to pseudo-Simple again.<\/p>\n Now what if we restart the log backup chain using a full database backup?<\/p>\n Perfect. Now what about if we break the chain and try to restart it using a differential database backup?<\/p>\n Perfect – that works too, as I’d expect. You may wonder why a differential backup works –\u00a0either a full or differential\u00a0backup will work as\u00a0they bridge the LSN gap since the last full or differential backup before the log backup chain was broken – both of these backups include transaction log – see More on how much transaction log a full backup includes<\/a>.<\/p>\n And here’s the script itself – enjoy!<\/p>\n\r\nCREATE DATABASE [SimpleModeDB];\r\nCREATE DATABASE [BulkLoggedModeDB];\r\nCREATE DATABASE [FullModeDB];\r\nGO\r\n\r\nALTER DATABASE [SimpleModeDB] SET RECOVERY SIMPLE;\r\nALTER DATABASE [BulkLoggedModeDB] SET RECOVERY BULK_LOGGED;\r\nALTER DATABASE [FullModeDB] SET RECOVERY FULL;\r\nGO\r\n\r\nSELECT\r\n [Name],\r\n msdb.dbo.SQLSkillsIsReallyInFullRecovery ([Name]) AS N'ReallyInFULL'\r\nFROM\r\n sys.databases\r\nWHERE\r\n [Name] LIKE N'%ModeDB';\r\nGO\r\n<\/pre>\n
\r\nName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ReallyInFULL\r\n----------------- -------------\r\nSimpleModeDB\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\nBulkLoggedModeDB\u00a0 0\r\nFullModeDB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\n<\/pre>\n
\r\nBACKUP DATABASE [FullModeDB]\r\nTO DISK = N'C:SQLskillsFullModeDB.bck'\r\nWITH INIT;\r\nGO\r\nSELECT msdb.dbo.SQLSkillsIsReallyInFullRecovery ('FullModeDB') AS N'ReallyInFULL';\r\nGO\r\n<\/pre>\n\r\nProcessed 152 pages for database 'FullModeDB', file 'FullModeDB' on file 1.\r\nProcessed 1 pages for database 'FullModeDB', file 'FullModeDB_log' on file 1.\r\nBACKUP DATABASE successfully processed 153 pages in 0.230 seconds (5.449 MB\/sec).\r\n\r\nReallyInFULL\r\n------------\r\n1\r\n<\/pre>\n
\r\nALTER DATABASE [FullModeDB] SET RECOVERY SIMPLE;\r\nALTER DATABASE [FullModeDB] SET RECOVERY FULL;\r\nGO\r\nSELECT msdb.dbo.SQLSkillsIsReallyInFullRecovery ('FullModeDB') AS N'ReallyInFULL';\r\nGO\r\n<\/pre>\n\r\nReallyInFULL\r\n------------\r\n0\r\n<\/pre>\n
\r\nBACKUP DATABASE [FullModeDB]\r\nTO DISK = N'C:SQLskillsFullModeDB.bck'\r\nWITH INIT;\r\nGO\r\nSELECT msdb.dbo.SQLSkillsIsReallyInFullRecovery ('FullModeDB') AS N'ReallyInFULL';\r\nGO\r\n<\/pre>\n\r\nProcessed 152 pages for database 'FullModeDB', file 'FullModeDB' on file 1.\r\nProcessed 1 pages for database 'FullModeDB', file 'FullModeDB_log' on file 1.\r\nBACKUP DATABASE successfully processed 153 pages in 0.095 seconds (13.193 MB\/sec).\r\n\r\nReallyInFULL\r\n------------\r\n1\r\n<\/pre>\n
\r\nALTER DATABASE [FullModeDB] SET RECOVERY SIMPLE;\r\nALTER DATABASE [FullModeDB] SET RECOVERY FULL;\r\nGO\r\nBACKUP DATABASE [FullModeDB]\r\nTO DISK = N'C:SQLskillsFullModeDB_diff.bck'\r\nWITH INIT, DIFFERENTIAL;\r\nGO\r\nSELECT msdb.dbo.SQLSkillsIsReallyInFullRecovery ('FullModeDB') AS N'ReallyInFULL';\r\nGO\r\n<\/pre>\n\r\nProcessed 40 pages for database 'FullModeDB', file 'FullModeDB' on file 1.\r\nProcessed 1 pages for database 'FullModeDB', file 'FullModeDB_log' on file 1.\r\nBACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.080 seconds (4.192 MB\/sec).<\/span>\r\n\r\nReallyInFULL\r\n------------\r\n1\r\n<\/pre>\n
\r\nUSE [msdb];\r\nGO\r\n\r\nIF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'SQLskillsIsReallyInFullRecovery')\r\n DROP FUNCTION [SQLskillsIsReallyInFullRecovery];\r\nGO\r\n\r\nCREATE FUNCTION [SQLskillsIsReallyInFullRecovery] (\r\n @DBName sysname)\r\nRETURNS BIT\r\nAS\r\nBEGIN\r\n DECLARE @IsReallyFull\u00a0\u00a0BIT;\r\n DECLARE @LastLogBackupLSN\u00a0NUMERIC (25,0);\r\n DECLARE @RecoveryModel\u00a0\u00a0TINYINT;\r\n\r\n SELECT\r\n @LastLogBackupLSN = [last_log_backup_lsn]\r\n FROM\r\n sys.database_recovery_status\r\n WHERE\r\n [database_id] = DB_ID (@DBName);\r\n\r\n SELECT\r\n @RecoveryModel = [recovery_model]\r\n FROM\r\n sys.databases\r\n WHERE\r\n [database_id] = DB_ID (@DBName);\r\n\r\n IF (@RecoveryModel = 1 AND @LastLogBackupLSN IS NOT NULL)\r\n SELECT @IsReallyFull = 1\r\n ELSE\r\n SELECT @IsReallyFull = 0;\r\n\r\n RETURN (@IsReallyFull);\r\nEND;\r\nGO\r\n<\/pre>\n