functions/Test-DbaFullRecoveryModel.ps1

function Test-DbaFullRecoveryModel {
    <#
        .SYNOPSIS
            Find if database is really in the Full recovery model or not.
 
        .DESCRIPTION
            When you switch a database into FULL recovery model, it will behave like a SIMPLE recovery model until a full backup is taken in order to begin a log backup chain. This state is also known as 'pseudo-Simple'.
 
            Inspired by Paul Randal's post (http://www.sqlskills.com/blogs/paul/new-script-is-that-database-really-in-the-full-recovery-mode/)
 
         .PARAMETER SqlInstance
            The SQL Server instance to connect to.
 
        .PARAMETER SqlCredential
            Allows you to login to servers using SQL Logins instead of Windows Authentication (AKA Integrated or Trusted). To use:
 
            $scred = Get-Credential, then pass $scred object to the -SqlCredential parameter.
 
            Windows Authentication will be used if SqlCredential is not specified. SQL Server does not accept Windows credentials being passed as credentials.
 
            To connect as a different Windows user, run PowerShell as that user.
 
        .PARAMETER Database
            Specifies the database(s) to process. Options for this list are auto-populated from the server. If unspecified, all databases will be processed.
 
        .PARAMETER ExcludeDatabase
            Specifies the database(s) to exclude from processing. Options for this list are auto-populated from the server.
 
        .PARAMETER Detailed
            If this switch is enabled, an additional "Notes" column is returned in the results.
 
        .NOTES
            Tags: DisasterRecovery, Backup
            Author: Claudio Silva (@ClaudioESSilva)
 
            Website: https://dbatools.io
            Copyright: (C) Chrissy LeMaire, clemaire@gmail.com
            License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0
 
        .LINK
            https://dbatools.io/Test-DbaFullRecoveryModel
 
        .EXAMPLE
            Test-DbaFullRecoveryModel -SqlInstance sql2005
 
            Shows all databases where the configured recovery model is FULL and indicates whether or not they are really in FULL recovery model.
 
        .EXAMPLE
            Test-DbaFullRecoveryModel -SqlInstance . | Where-Object {$_.ActualRecoveryModel -ne "FULL"}
 
            Only shows the databases that are in 'pseudo-simple' mode.
 
        .EXAMPLE
            Test-DbaFullRecoveryModel -SqlInstance sql2008 | Sort-Object Server, ActualRecoveryModel -Descending
 
            Shows all databases where the configured recovery model is FULL and indicates whether or not they are really in FULL recovery model. The Sort-Object will cause the databases in 'pseudo-simple' mode to show first.
    #>

    [CmdletBinding()]
    [OutputType("System.Collections.ArrayList")]
    Param (
        [parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias("ServerInstance", "SqlServer")]
        [DbaInstanceParameter[]]$SqlInstance,
        [Alias("Databases")]
        [object[]]$Database,
        [object[]]$ExcludeDatabase,
        [PSCredential]$SqlCredential,
        [switch]$Detailed
    )

    begin {
        $collection = New-Object System.Collections.ArrayList
    }

    process {
        foreach ($servername in $SqlInstance) {
            try {
                $server = Connect-SqlInstance -SqlInstance $servername -SqlCredential $SqlCredential

                if ($server.versionMajor -lt 9) {
                    Write-Warning "This function does not support versions lower than SQL Server 2005 (v9). Skipping server '$servername'."
                    continue
                }

                $sqlRecoveryModel = "SELECT '$($server.Name)' AS 'Server'
                        , d.[name] AS [Database]
                        , d.recovery_model AS RecoveryModel
                        , d.recovery_model_desc AS RecoveryModelDesc
                        , CASE
                            WHEN d.recovery_model = 1 AND drs.last_log_backup_lsn IS NOT NULL THEN 1
                            ELSE 0
                           END AS IsReallyInFullRecoveryModel
                  FROM sys.databases AS D
                    INNER JOIN sys.database_recovery_status AS drs
                       ON D.database_id = drs.database_id
                  WHERE d.recovery_model = 1"


                if ($Database) {
                    $dblist = $Database -join "','"
                    $databasefilter += "AND d.[name] in ('$dblist')"
                }
                if ($ExcludeDatabase) {
                    $dblist = $ExcludeDatabase -join "','"
                    $databasefilter += "AND d.[name] NOT IN ('$dblist')"
                }

                $sql = "$sqlRecoveryModel $databasefilter"

                Write-Debug $sql

                $recoverymodel = $server.Databases['master'].ExecuteWithResults($sql)

                if ($recoverymodel.Tables[0].Rows.Count -eq 0) {
                    Write-Output "Server '$servername' does not have any databases in FULL recovery model."
                }
                else {
                    foreach ($recoverymodelrow in $recoverymodel.Tables[0]) {
                        if (!([bool]$recoverymodelrow.IsReallyInFullRecoveryModel)) {
                            $notes = "Database is still in SIMPLE recovery model until a full database backup is taken."
                            $ActualRecoveryModel = "pseudo-SIMPLE"
                        }
                        else {
                            $notes = $null
                            $ActualRecoveryModel = "FULL"
                        }

                        $null = $collection.Add([PSCustomObject]@{
                                Server                  = $recoverymodelrow.Server
                                Database                = $recoverymodelrow.Database
                                ConfiguredRecoveryModel = $recoverymodelrow.RecoveryModelDesc
                                ActualRecoveryModel     = $ActualRecoveryModel
                                Notes                   = $notes
                            })
                    }
                }
            }
            catch {
                throw $_
            }
        }
    }

    end {
        if ($Detailed) {
            return $collection
        }
        else {
            return ($collection | Select-Object * -ExcludeProperty notes)
        }
    }
}