Get-DbaDbRecoveryModel

Want to see the source code for this command? Check out Get-DbaDbRecoveryModel on GitHub.
Want to see the Bill Of Health for this command? Check out Get-DbaDbRecoveryModel.

Synopsis

Retrieves database recovery model settings and backup history information from SQL Server instances.

Description

Retrieves recovery model configuration for databases along with their last backup dates, which is essential for backup strategy planning and compliance auditing. DBAs use this to identify databases with inappropriate recovery models for their business requirements, troubleshoot transaction log growth issues, and ensure backup policies align with recovery model settings. The function shows whether databases are accessible and when their last full, differential, and transaction log backups occurred, making it valuable for both routine maintenance and disaster recovery planning.

Syntax

Get-DbaDbRecoveryModel
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-RecoveryModel] <String[]>]
    [[-Database] <Object[]>]
    [[-ExcludeDatabase] <Object[]>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaDbRecoveryModel -SqlInstance sql2014 -RecoveryModel BulkLogged -Verbose

Gets all databases on SQL Server instance sql2014 having RecoveryModel set to BulkLogged.

Example: 2

PS C:\> Get-DbaDbRecoveryModel -SqlInstance sql2014 -Database TestDB

Gets recovery model information for TestDB. If TestDB does not exist on the instance nothing is returned.

Required Parameters

-SqlInstance

The target SQL Server instance or instances.

Optional Parameters

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-RecoveryModel

Filters results to show only databases using the specified recovery model (Simple, Full, or BulkLogged).
Use this to identify databases with incorrect recovery models for your backup strategy or to audit compliance with recovery model policies.
Details about the recovery models can be found here:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server

-Database

Specifies which databases to retrieve recovery model information for. Accepts database names, wildcards, or arrays.
Use this when you need to check recovery models for specific databases rather than all databases on the instance.

-ExcludeDatabase

Excludes specific databases from the recovery model check. Accepts database names, wildcards, or arrays.
Useful for skipping system databases or databases you don't manage when reviewing recovery model compliance.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.