New script: is that database REALLY in the FULL recovery mode?

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’).

It’s a problem for several reasons:

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

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)

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.

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 field in the database boot page (see Search Engine Q&A #20: Boot pages, and boot page corruption) but also nicely available in the DMV sys.database_recovery_status.

I’ve tested this on 2005 SP3 and 2008 SP1.

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 property returned by DATABASEPROPERTY to be correct when the database is in pseudo-Simple, but it’s not unfortunately.

Here are some test cases for the script:

CREATE DATABASE [SimpleModeDB];
CREATE DATABASE [BulkLoggedModeDB];
CREATE DATABASE [FullModeDB];
GO

ALTER DATABASE [SimpleModeDB] SET RECOVERY SIMPLE;
ALTER DATABASE [BulkLoggedModeDB] SET RECOVERY BULK_LOGGED;
ALTER DATABASE [FullModeDB] SET RECOVERY FULL;
GO

SELECT
    [Name],
    msdb.dbo.SQLSkillsIsReallyInFullRecovery ([Name]) AS N'ReallyInFULL'
FROM
    sys.databases
WHERE
    [Name] LIKE N'%ModeDB';
GO

Name              ReallyInFULL
----------------- -------------
SimpleModeDB      0
BulkLoggedModeDB  0
FullModeDB        0

This makes sense – the new FullModeDB database is still in pseudo-Simple. Now what if we take a full database backup?

BACKUP DATABASE [FullModeDB]
TO DISK = N'C:SQLskillsFullModeDB.bck'
WITH INIT;
GO
SELECT msdb.dbo.SQLSkillsIsReallyInFullRecovery ('FullModeDB') AS N'ReallyInFULL';
GO

Processed 152 pages for database 'FullModeDB', file 'FullModeDB' on file 1.
Processed 1 pages for database 'FullModeDB', file 'FullModeDB_log' on file 1.
BACKUP DATABASE successfully processed 153 pages in 0.230 seconds (5.449 MB/sec).

ReallyInFULL
------------
1

Perfect. Now what about switching it back to Simple and back to Full again?

ALTER DATABASE [FullModeDB] SET RECOVERY SIMPLE;
ALTER DATABASE [FullModeDB] SET RECOVERY FULL;
GO
SELECT msdb.dbo.SQLSkillsIsReallyInFullRecovery ('FullModeDB') AS N'ReallyInFULL';
GO

ReallyInFULL
------------
0

Just as we expect – the log backup chain has been broken and the database is back to pseudo-Simple again.

Now what if we restart the log backup chain using a full database backup?

BACKUP DATABASE [FullModeDB]
TO DISK = N'C:SQLskillsFullModeDB.bck'
WITH INIT;
GO
SELECT msdb.dbo.SQLSkillsIsReallyInFullRecovery ('FullModeDB') AS N'ReallyInFULL';
GO

Processed 152 pages for database 'FullModeDB', file 'FullModeDB' on file 1.
Processed 1 pages for database 'FullModeDB', file 'FullModeDB_log' on file 1.
BACKUP DATABASE successfully processed 153 pages in 0.095 seconds (13.193 MB/sec).

ReallyInFULL
------------
1

Perfect. Now what about if we break the chain and try to restart it using a differential database backup?

ALTER DATABASE [FullModeDB] SET RECOVERY SIMPLE;
ALTER DATABASE [FullModeDB] SET RECOVERY FULL;
GO
BACKUP DATABASE [FullModeDB]
TO DISK = N'C:SQLskillsFullModeDB_diff.bck'
WITH INIT, DIFFERENTIAL;
GO
SELECT msdb.dbo.SQLSkillsIsReallyInFullRecovery ('FullModeDB') AS N'ReallyInFULL';
GO

Processed 40 pages for database 'FullModeDB', file 'FullModeDB' on file 1.
Processed 1 pages for database 'FullModeDB', file 'FullModeDB_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.080 seconds (4.192 MB/sec).</span>

ReallyInFULL
------------
1

Perfect – that works too, as I’d expect. You may wonder why a differential backup works – either a full or differential backup will work as they 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.

And here’s the script itself – enjoy!

USE [msdb];
GO

IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'SQLskillsIsReallyInFullRecovery')
    DROP FUNCTION [SQLskillsIsReallyInFullRecovery];
GO

CREATE FUNCTION [SQLskillsIsReallyInFullRecovery] (
    @DBName sysname)
RETURNS BIT
AS
BEGIN
    DECLARE @IsReallyFull  BIT;
    DECLARE @LastLogBackupLSN NUMERIC (25,0);
    DECLARE @RecoveryModel  TINYINT;

    SELECT
        @LastLogBackupLSN = [last_log_backup_lsn]
    FROM
        sys.database_recovery_status
    WHERE
        [database_id] = DB_ID (@DBName);

    SELECT
        @RecoveryModel = [recovery_model]
    FROM
        sys.databases
    WHERE
        [database_id] = DB_ID (@DBName);

    IF (@RecoveryModel = 1 AND @LastLogBackupLSN IS NOT NULL)
        SELECT @IsReallyFull = 1
    ELSE
        SELECT @IsReallyFull = 0;

    RETURN (@IsReallyFull);
END;
GO

Edwin Sarmiento has published a PowerShell script that does the same thing – see here.