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


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.

From PowerShell, execute   Get-Help Test-DbaFullRecoveryModel -Detailed   for more information on this function. Want to see the source code? See it on GitHub