Commands / Invoke-sqmSetDatabaseRecoveryMode
Invoke-sqmSetDatabaseRecoveryMode
ConfigurationsqmSQLTool v1.8.2+ · Database Config⚠ High Impact  ✓ Pre-checks
Changes database recovery mode (FULL, BULK_LOGGED, SIMPLE) with validation and optional transaction log backup before the change. Validates target recovery mode, checks database status, and optionally performs a backup of the transaction log to avoid data loss when transitioning from FULL to other modes.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional, Target SQL Server instance.
-SqlCredentialPSCredentialOptional, Credentials for the connection.
-Databasestring[]Optional, Target databases (name or wildcard pattern).
-RecoveryModestringRequired, Target mode: FULL | BULK_LOGGED | SIMPLE
-EnableExceptionswitchSwitch$falseRe-throw exceptions immediately.
-WhatIf / -ConfirmswitchOptional, ShouldProcess (ConfirmImpact: High) guards mode change.
-AllswitchSwitch$falseWhen set, changes the recovery mode for all user databases.

Execution Flow

START dbatools available? NO throw: dbatools not found YES Validate -RecoveryMode parameter Must be: FULL | BULK_LOGGED | SIMPLE foreach $db in $databases Get database properties: status, current recovery mode Verify database is online (not RESTORING, RECOVERING, etc.) PRE-CHECKS • Is DB already in target recovery mode? (skip if yes) • Is DB online? (abort if offline) • For AG members: trigger on primary only TODAY --> Current = FULL & -BackupLogBefore? NO YES Backup-DbaDatabase -Type Log to -BackupPath Flush open transactions before mode change ShouldProcess? (ConfirmImpact: High) NO WhatIf: show change continue YES ALTER DATABASE -RecoveryMode Example: ALTER DATABASE [MyDB] SET RECOVERY FULL Return [PSCustomObject[]] per database Database, OldRecoveryMode, NewRecoveryMode, LogBackupFile, Status DONE

Examples

Change to SIMPLE recovery (with log backup first)
Invoke-sqmSetDatabaseRecoveryMode -SqlInstance "SQL01" -Database "MyDB" -RecoveryMode "SIMPLE" -BackupLogBefore
Change multiple databases with -WhatIf
Invoke-sqmSetDatabaseRecoveryMode -SqlInstance "SQL01" -Database "Dev*" -RecoveryMode "BULK_LOGGED" -WhatIf
← Back
Command Index