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.

8 thoughts on “New script: is that database REALLY in the FULL recovery mode?

  1. Paul,
    I was reviewing Edwin Sarmiento’s POSH tip http://www.mssqltips.com/sqlservertip/2974/check-if-a-sql-server-database-is-in-pseudosimple-recovery-model-using-windows-powershell/ and arrived at this page trying to resolve an issue with his scripts: they did not return the expected data.

    To make a long story short, I first ran his code on SQL 2008 10.0.5770. I then tried DBCC DBINFO with table results and did not get ‘dbi_dbbackupLSN’ in the VALUE column, but in WAS the OBJECT column. I then ran the DBCC on SQL 2012 11.0.3349 and ‘dbi_dbbackupLSN’ was in the VALUE column as shown in Edwin’s script. Running DBCC DBINFO on both instances this time without table results showed the page formatted differently and including ‘dbi_dbbackupLSN’. So either DBCC DBINFO has changed, the boot page has changed or both.

    As you always point out with undocumented commands is they are undocumented for a reason, and cannot be totally or safely trusted. I thought you might want to amend your post http://www.sqlskills.com/blogs/paul/search-engine-qa-20-boot-pages-and-boot-page-corruption/ ‘This command is undocumented and unsupported but widely known and ‘documented’ in lots of places on the web – given that it uses the same code as DBCC PAGE, it’s just as safe to use IMHO.’

    Thanks for all you do!

  2. Thanks, Paul, for adding a reference to the PowerShell script on MSSQLTips.com. Apparently, as Carm mentioned, the dbi_dbbackupLSN field does not exist on a database running on SQL Server 2008 R2 with SP2 as well. I guess the way to go is to use the sys.database_recovery_status DMV

  3. This script was helpful to me. It uses your SQLskillsIsReallyInFullRecovery function to show actual and perceived recovery models.

    SELECT
    d.name,
    COALESCE(case d.recovery_model
    WHEN 1 then NULL
    WHEN 2 then ‘BULK_LOGGED’
    WHEN 3 then ‘SIMPLE’
    ELSE ”
    END,
    case [msdb].[dbo].[SQLskillsIsReallyInFullRecovery] (d.name)
    WHEN 1 then ‘FULL’
    ELSE ‘psuedo-SIMPLE’
    END) as ‘Real Recovery Model’,
    d.recovery_model_desc as ‘Perceived Recovery Model’
    from sys.databases d
    GO

  4. Hi Paul,
    log chain will be intact only when there was no checkpoint during when we switched to simple recovery model right?

    I did a checkpoint during when i switched to simple and changed to full , differential backup brought database to full mode,but log chain is not intact.

  5. Hi Paul,

    Can you think of any reason why changing from SIMPLE to FULL would make an error go away. We’ve an intermittent error “A system assertion check has failed” being thrown in SQL 2016 SP1 CU8. The DB is in SIMPLE. While researching we came across a comment “switch to FULL recovery fixes it”. We tried that and it have (for now) fixed the issue, though we continue to monitor.

    The error is apparently related to using sql MERGE and we found reference to bug in 2016 SP1 but apparently fixed in CU1 though not our one.

    Just trying to work out why changing from SIMPLE to FULL influence this? Even before performing a backup to force active FULL is being used.

    Thanks, Paul

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.