· Uwe Janke
Commands / Export-sqmDatabaseSettings

Export-sqmDatabaseSettings

SQL ConfigurationsqmSQLTool v1.8.2+ · ExportJSON + HTML Export  ✅ -WhatIf supported
Exports the per-database "Options" settings - the same settings shown on the Options page of SSMS' Database Properties dialog: Recovery Model, Compatibility Level, Page Verify, the ANSI/ARITHABORT/AUTO_* toggles, Trustworthy, Delayed Durability, Target Recovery Time, Read-Only, Read-Committed-Snapshot, Broker-Enabled, and more - to a JSON snapshot file, with a customer-presentable HTML companion report (summary table plus a collapsible per-database detail section). Everything comes from one query against sys.databases; no need to connect into each database individually. System databases (master/model/msdb/tempdb) are excluded by default. The counterpart, Import-sqmDatabaseSettings, re-applies a saved snapshot via ALTER DATABASE.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringRequired, Source SQL Server instance.
-SqlCredentialPSCredentialOptional, Optional alternative credentials.
-Databasestring[]Optional, Restrict the export to these database names (wildcards allowed). Without this, every database is exported (subject to the two switches below).
-ExcludeDatabasestring[]Optional, Additional database names to exclude (wildcards allowed).
-IncludeSystemDatabasesswitchSwitch$falseWhen set, master/model/msdb/tempdb are included in the export.
-OutputPathstringRequired, A full file path, or an existing/creatable directory - in the latter case the file is auto-named DatabaseSettings_<Instance>_<timestamp>.json. An HTML report with the same base name (.html) is written alongside it.
-NoOpenswitchSwitch$falseSuppress automatically opening the generated HTML report.
-EnableExceptionswitchSwitch$falseThrow exceptions immediately instead of returning a Failed result object.
-WhatIf / -ConfirmswitchOptional, ShouldProcess guard on the file-writing step. Shows what would be written (path, database count) without creating any file.

Execution Flow

START dbatools installed? NO throw: dbatools not found YES Connect-DbaInstance, then query sys.databases One SELECT, columns driven by Get-sqmDatabaseSettingsDefinition covers every candidate database in a single round trip Filter result rows database_id > 4 unless -IncludeSystemDatabases · then -Database, then -ExcludeDatabase Zero rows after filters? YES return Status='Skipped' Write-Warning, no file written NO Build one settings object per database Bit-typed columns (ANSI_NULLS, AUTO_CLOSE, ...) cast to bool Resolve -OutputPath: directory or full file path? Directory -> auto-named DatabaseSettings_<Instance>_<timestamp>.json ShouldProcess (-WhatIf)? SKIP return Status='WhatIf' nothing written RUN Write JSON snapshot (ConvertTo-Json -Depth 6) Build HTML companion: summary table + one collapsible <details> per database Invoke-sqmOpenReport (unless -NoOpen) Return [PSCustomObject] OutputFile · ReportPath · DatabaseCount · Status='Success' DONE

Examples

Export the Options settings of every user database
Export-sqmDatabaseSettings -SqlInstance 'SQL01' -OutputPath 'C:\Backups\SQLSnapshots'
Export a single database's settings to an explicit file
Export-sqmDatabaseSettings -SqlInstance 'SQL01' -Database 'SalesDB' -OutputPath 'C:\Backups\SalesDB_Options.json'
Preview what would be exported, without writing anything
Export-sqmDatabaseSettings -SqlInstance 'SQL01' -OutputPath 'C:\Backups\SQLSnapshots' -WhatIf
← Back
Command Index
Live Example
View Report