Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (3)   |   Related Tips: More > Database Configurations


SQL Server Conference Giveaway - click to learn more


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.

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
}

I introduced line breaks together with the Format-List cmdlet for ease of readability

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Edwin Sarmiento 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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, April 13, 2015 - 10:05:28 AM - bass_player Back To Top

Hi Steve,

If you are running SQL Server 2008, you will get different results compared to SQL Server 2012 and higher. Take a look at the results of DBCC DBINFO and see the value of dbi_dbbackupLSN


Saturday, April 11, 2015 - 8:57:04 AM - Steve Evans Back To Top

Ewin,

When running your Powershell script as stated, the script yields no results. I am unsung Win Server and SSMS 2008R2.

I tried plugging my instance name into the code as ' foreach($db in ($sql01.Databases | etc... As sql01 is my instance name, but still no results.

Any ideas?


Friday, July 19, 2013 - 11:15:41 AM - Carm Vecchio Back To Top

Edwin, this is a great tip; however DBCC DBINFO return different information for SQL 2008 and SQL 2012. I guess that's why this is officially "undocumented" :-)

I ran your POSH script against my SQL2008 instance and never got anything back. I went to Paul's article and everything worked fine using the system tables. I ran the DBCC into a temp table and the expected FIELD value was not there. I found it in the OBJECT column.

I did my testing of the boot page using the MASTER database from SQL 2008 and SQL2012 (hoping) to take any 'compatibility upgrades' out of the mix. I got the same results using AdventureWorks2008 and AdventureWorks2012.

Here's the output of DBCC DBINFO by SQL Server Version:

SQL Server 2008 10.0.55770
Master Database
DBCC DBINFO () WITH TABLERESULTS

ParentObject Object   Field   VALUE
DBINFO @0x000000000F04E670 dbi_dbbackupLSN  m_fSeqNo  0
DBINFO @0x000000000F04E670 dbi_dbbackupLSN  m_blockOffset  0
DBINFO @0x000000000F04E670 dbi_dbbackupLSN  m_slotId  0

 

SQL Server 2012 11.0.3349
Master Database
DBCC DBINFO () WITH TABLERESULTS

ParentObject   Object     Field   VALUE
DBINFO STRUCTURE:  DBINFO @0x000000001632BE60 dbi_dbbackupLSN 0:0:0 (0x00000000:00000000:0000)

 

I reran DBCC DBINFO () this time without TABLE RESULTS and found the returned "page layout" is different

SQL2008 Master Database

 

dbi_dbbackupLSN

 

m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0

 

 

SQL 2012 Master Database 

 

dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000)

 

 

 

 

 

So I guess the takeaway is these are undocumented for a reason and don't blindly trust something that worked in previous versions. Please let me know if you came up with the same thing ;-)

 

 

 

Great POSH tips! Keep it up. I hope to see you at another Dev Con.

 

 

 

Carm Vecchio

 

Learn more about SQL Server tools