dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Test-DbaFullRecoveryModel

What is a Recovery Model?

From msdn Recovery Models (SQL Server)

Why is this command necessary?

When we set database recovery model to FULL, only after the first FULL backup the database becomes really on FULL recovery model. Until then, the database will be in a recovery model that is commonly called ‘pseudo-SIMPLE’ because it will still behave like a database in SIMPLE recovery model where transaction log will still get truncated.

Also while in ‘pseudo-SIMPLE’ mode, if you try to take a log backup you will get the following error

— conclusion sentence about this could lead to unexpected behavior/data loss —

How do we test it?

We have implement the algorithm shared on Paul Randal’s blog post New script: is that database REALLY in the FULL recovery mode? which validate the ‘last_log_backup_lsn’ column on ‘sys.database_recovery_status’ along with ‘recovery_model’ from ‘sys.databases’.

Nice! So, how can I resolve the ‘pseudo-SIMPLE’ cases?

Just perform a FULL database backup. Then don’t forget to take log backups so the log can be reused and don’t grow out of control.

Screenshots

test-dbafullrecoverymodel

Examples

Shows all databases which actual configured recovery model is FULL and says if they are really in FULL recovery model or not.

Test-DbaFullRecoveryModel -SqlServer sql2005

Only shows the databases that are in ‘pseudo-simple’ mode.

 Test-DbaFullRecoveryModel -SqlServer sql2012, sql2016 | Where-Object {$_.ActualRecoveryModel -ne “FULL”}

Shows all databases from both servers which actual configured recovery model is FULL and says if they are really in FULL recovery model or not. Will show in first place the ones that are in ‘pseudo-simple’ mode.

 Test-DbaFullRecoveryModel -SqlServer sql2008 | Sort-Object Server, ActualRecoveryModel -Descending

This command was created by Cláudio Silva. You can find Cláudio on Twitter and LinkedIn.

Get-Help

From PowerShell, execute Get-Help Test-DbaFullRecoveryModel -Detailed for more information on this function.

Source Code

Want to see the source code? View Test-DbaFullRecoveryModel.ps1 on GitHub
 

Related commands