Check If a SQL Server Database Is In Pseudo-Simple Recovery Model Using Windows PowerShell

By: Edwin Sarmiento | Comments (3) | Related: > Database Configurations

Problem

I wanted to know if my databases are really in FULL recovery model. I've heard about this recovery model called pseudo-simple where the database still behaves like it is still in SIMPLE recovery model until a full database backup is taken. How do I know if my databases are really in FULL recovery model? Check out this tip to learn more.

Solution

Paul Randal, CEO of SQLSkills.com, wrote a blog post about this recovery model called pseudo-simple. This is a behavior of the database that, while it may be configured to be in FULL recovery model, still behaves as if it was still in SIMPLE recovery model. You can easily test this by creating a database and setting it to FULL recovery model. While you can perform a lot of transactions in the database, the transaction log will get truncated, as it is with databases in SIMPLE recovery model. The good thing about this is that this state can be easily checked by looking at the last_log_backup_lsn column of the sys.database_recovery_status DMV. But this would require querying both the sys.database_recovery_status DMV and the sys.databases DMV and then checking if the database is in FULL recovery model (Paul's blog post actually has a TSQL script available to do this).

This is where Windows PowerShell can make this task relatively easy. Similar to the previous tip on Check DBCC CHECKDB Last Execution Using Windows PowerShell, we can take advantage of using the undocumented command DBCC DBINFO to read the database boot page and check the value of the dbi_dbbackupLSN field. Let's see how we can simplify this task by using PowerShell and SMO.

We'll start by loading the appropriate .NET assemblies used by SMO as defined in this previous tip. (If you read the other Windows PowerShell tips, you'll see the repeating trend of using the same code for loading the SMO assemblies, creating a SQL Server instance object and creating a database object.)

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

Next, we'll create an instance of the Server class to represent the SQL Server instance that you will be connecting to, passing the SQL Server instance name. I will be assigning the results to a variable named $serverInstance so we can access the different properties and methods of the Server class.

$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "InstanceName"

We will, then, iterate thru all of the databases in the SQL Server instance by accessing the Databases property of the Server class. This property represents a collection of database objects defined in the SQL Server instance we've specified. While iterating thru the collection of databases, we will use the ExecuteWithResults method of the Database class to execute the DBCC DBINFO command in the context of the current database (we will use the WITH TABLERESULTS option used with DBCC commands to filter according to a specific field value.) The results of this method is a .NET object type called a DataSet which is commonly used in ADO.NET. A DataSet is an in-memory cache of data with a collection of DataTable objects. Think of it as an in-memory version of a database with different tables, columns, relationships, etc. If there are multiple tables in the resultset, we can access each table using an index in an array. However, since the results of the DBCC DBINFO command is a single resultset that mimics a table, think of the results of the ExecuteWithResults method as an in-memory table. The PowerShell code to accomplish all of these tasks is shown below.

foreach($db in ($serverInstance.Databases | Where-Object {$_.IsSystemObject -eq $false}))
{
$db.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0]}

In order to access the table results, I used the zero index since I did not explicitly define the name of the DataTable that will hold the results of the DBCC DBINFO command. The results of running the DBCC DBINFO T-SQL command is shown below.

The results of running the DBCC DBINFO T-SQL command is shown below

The results of running the PowerShell script is shown below.

I've highlighted the field that I've mentioned earlier in this tip - dbi_dbbackupLSN. This field contains the value of the last log sequence number (LSN) that has been backed up. We need to filter the result set of the DBCC DBINFO command to display only this field and its corresponding value. Fortunately, PowerShell has the Where-Object cmdlet to filter the results of object collections based on their property values. We can use the Where-Object cmdlet to filter the results returned by the ExecuteWithResults method for a particular database and use the field named dbi_dbbackupLSN. To make it easier, we will pipe the results of the ExecuteWithResults method to the Where-Object cmdlet. I've also included the Select-Object cmdlet to display the name of the database and the value of the dbi_dbbackupLSN field.

foreach($db in ($serverInstance.Databases | Where-Object {$_.IsSystemObject -eq $false}))
{
$db.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | Where-Object {$_.Field -eq "dbi_dbbackupLSN"}  | Select-Object $db.Name, Value
}

NOTE: In the screenshot, I introduced line breaks together with the Format-List cmdlet for ease of readability. However, the command can be written in a single line of code, which is one of the key strengths of Windows PowerShell.

The result now displays what I want - the name of the database and the value of the dbi_dbbackupLSN field - in just 7 lines of PowerShell code (I can even write this in 3 lines and have the entire foreach loop in a single line, but that would be annoying and hard to read) that does not query both the sys.database_recovery_status DMV and the sys.databases DMV.

But we're not done yet. We have to know the correct value of the dbi_dbbackupLSN field that we can use to identify whether or not the database is still in pseudo-simple recovery model. If you noticed in the screenshot, a value of 0:0:0 (0x00000000:00000000:0000) means that we don't have a LSN value of the latest backup for that database. This is the same as having a NULL value in the last_log_backup_lsn column of the sys.database_recovery_status DMV. We can use this value to evaluate the dbi_dbbackupLSN field to check whether or not our database is in pseudo-simple recovery model.

Let's first assign the results of the ExecuteWithResults method in a variable named $isDBinPseudoSimple.

$isDBinPseudoSimple=$db.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | Where-Object {$_.Field -eq "dbi_dbbackupLSN"} | Select-Object $db.Name, Value

We, then, check if the value of the $isDBinPseudoSimple variable is the same as 0:0:0 (0x00000000:00000000:0000) (if you're lazy like me, you'll probably just evaluate the numeric values before the parenthesis).

    if (($isDBinPseudoSimple.Value) -eq "0:0:0 (0x00000000:00000000:0000)")  #Or this can be if (($isDBinPseudoSimple.Value) -like "0:0:0*")  
    {
        Write-Host $db.Name "is in pseudo-simple recovery model"
    }

And since we only want those databases in pseudo-simple recovery model, we also check if their recovery model is FULL.

    if ((($isDBinPseudoSimple.Value) -eq "0:0:0 (0x00000000:00000000:0000)") -and ($db.RecoveryModel -eq "Full"))  #Or this can be if (($isDBinPseudoSimple.Value) -like "0:0:0*")  
    {
        Write-Host $db.Name "is in pseudo-simple recovery model"
    }

Here's the complete code listing.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "localhost"
foreach($db in ($serverInstance.Databases | Where-Object {$_.IsSystemObject -eq $false}))
{
$isDBinPseudoSimple=$db.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | Where-Object {$_.Field -eq "dbi_dbbackupLSN"} | Select-Object $db.Name, Value
    if ((($isDBinPseudoSimple.Value) -eq "0:0:0 (0x00000000:00000000:0000)") -and ($db.RecoveryModel -eq "Full"))  #Or this can be if (($isDBinPseudoSimple.Value) -like "0:0:0*")  
    {
        Write-Host $db.Name "is in pseudo-simple recovery model"
    }
}

Let's first assign the results of the ExecuteWithResults method in a variable named $isDBinPseudoSimple

Next Steps

Review the previous tip on Check DBCC CHECKDB Last Execution Using Windows PowerShell and all PowerShell-related tips.

About the author

Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Check If a SQL Server Database Is In Pseudo-Simple Recovery Model Using Windows PowerShell

Problem

Solution

Next Steps

About the author

Comments For This Article