Set‑SqlDscDatabaseProperty - dsccommunity/SqlServerDsc GitHub Wiki

SYNOPSIS

Sets properties of a database in a SQL Server Database Engine instance.

SYNTAX

ServerObjectSet (Default)

Set-SqlDscDatabaseProperty -ServerObject <Server> -Name <String> [-Refresh]
 [-AcceleratedRecoveryEnabled <Boolean>] [-AnsiNullDefault <Boolean>] [-AnsiNullsEnabled <Boolean>]
 [-AnsiPaddingEnabled <Boolean>] [-AnsiWarningsEnabled <Boolean>] [-ArithmeticAbortEnabled <Boolean>]
 [-AutoClose <Boolean>] [-AutoCreateIncrementalStatisticsEnabled <Boolean>]
 [-AutoCreateStatisticsEnabled <Boolean>] [-AutoShrink <Boolean>] [-AutoUpdateStatisticsAsync <Boolean>]
 [-AutoUpdateStatisticsEnabled <Boolean>] [-BrokerEnabled <Boolean>] [-ChangeTrackingAutoCleanUp <Boolean>]
 [-ChangeTrackingEnabled <Boolean>] [-CloseCursorsOnCommitEnabled <Boolean>]
 [-ConcatenateNullYieldsNull <Boolean>] [-DatabaseOwnershipChaining <Boolean>]
 [-DataRetentionEnabled <Boolean>] [-DateCorrelationOptimization <Boolean>] [-EncryptionEnabled <Boolean>]
 [-HonorBrokerPriority <Boolean>] [-IsFullTextEnabled <Boolean>] [-IsLedger <Boolean>]
 [-IsParameterizationForced <Boolean>] [-IsReadCommittedSnapshotOn <Boolean>] [-IsSqlDw <Boolean>]
 [-IsVarDecimalStorageFormatEnabled <Boolean>] [-LegacyCardinalityEstimation <Boolean>]
 [-LegacyCardinalityEstimationForSecondary <Boolean>] [-LocalCursorsDefault <Boolean>]
 [-NestedTriggersEnabled <Boolean>] [-NumericRoundAbortEnabled <Boolean>] [-ParameterSniffing <Boolean>]
 [-ParameterSniffingForSecondary <Boolean>] [-QueryOptimizerHotfixes <Boolean>]
 [-QueryOptimizerHotfixesForSecondary <Boolean>] [-QuotedIdentifiersEnabled <Boolean>] [-ReadOnly <Boolean>]
 [-RecursiveTriggersEnabled <Boolean>] [-RemoteDataArchiveEnabled <Boolean>]
 [-RemoteDataArchiveUseFederatedServiceAccount <Boolean>] [-TemporalHistoryRetentionEnabled <Boolean>]
 [-TransformNoiseWords <Boolean>] [-Trustworthy <Boolean>] [-ChangeTrackingRetentionPeriod <Int32>]
 [-DefaultFullTextLanguage <Int32>] [-DefaultLanguage <Int32>] [-MaxDop <Int32>] [-MaxDopForSecondary <Int32>]
 [-MirroringRedoQueueMaxSize <Int32>] [-MirroringTimeout <Int32>] [-TargetRecoveryTime <Int32>]
 [-TwoDigitYearCutoff <Int32>] [-MaxSizeInBytes <Double>] [-Collation <String>]
 [-FilestreamDirectoryName <String>] [-MirroringPartner <String>] [-MirroringPartnerInstance <String>]
 [-MirroringWitness <String>] [-PersistentVersionStoreFileGroup <String>] [-PrimaryFilePath <String>]
 [-RemoteDataArchiveCredential <String>] [-RemoteDataArchiveEndpoint <String>]
 [-RemoteDataArchiveLinkedServer <String>] [-RemoteDatabaseName <String>]
 [-ChangeTrackingRetentionPeriodUnits <RetentionPeriodUnits>] [-CompatibilityLevel <CompatibilityLevel>]
 [-ContainmentType <ContainmentType>] [-DelayedDurability <DelayedDurability>]
 [-FilestreamNonTransactedAccess <FilestreamNonTransactedAccessType>]
 [-MirroringSafetyLevel <MirroringSafetyLevel>] [-PageVerify <PageVerify>] [-RecoveryModel <RecoveryModel>]
 [-UserAccess <DatabaseUserAccess>] [-Force] [-PassThru] [-WhatIf]
 [-Confirm] [<CommonParameters>]

DatabaseObjectSet

Set-SqlDscDatabaseProperty -DatabaseObject <Database> [-AcceleratedRecoveryEnabled <Boolean>]
 [-AnsiNullDefault <Boolean>] [-AnsiNullsEnabled <Boolean>] [-AnsiPaddingEnabled <Boolean>]
 [-AnsiWarningsEnabled <Boolean>] [-ArithmeticAbortEnabled <Boolean>] [-AutoClose <Boolean>]
 [-AutoCreateIncrementalStatisticsEnabled <Boolean>] [-AutoCreateStatisticsEnabled <Boolean>]
 [-AutoShrink <Boolean>] [-AutoUpdateStatisticsAsync <Boolean>] [-AutoUpdateStatisticsEnabled <Boolean>]
 [-BrokerEnabled <Boolean>] [-ChangeTrackingAutoCleanUp <Boolean>] [-ChangeTrackingEnabled <Boolean>]
 [-CloseCursorsOnCommitEnabled <Boolean>] [-ConcatenateNullYieldsNull <Boolean>]
 [-DatabaseOwnershipChaining <Boolean>] [-DataRetentionEnabled <Boolean>]
 [-DateCorrelationOptimization <Boolean>] [-EncryptionEnabled <Boolean>] [-HonorBrokerPriority <Boolean>]
 [-IsFullTextEnabled <Boolean>] [-IsLedger <Boolean>] [-IsParameterizationForced <Boolean>]
 [-IsReadCommittedSnapshotOn <Boolean>] [-IsSqlDw <Boolean>] [-IsVarDecimalStorageFormatEnabled <Boolean>]
 [-LegacyCardinalityEstimation <Boolean>] [-LegacyCardinalityEstimationForSecondary <Boolean>]
 [-LocalCursorsDefault <Boolean>] [-NestedTriggersEnabled <Boolean>] [-NumericRoundAbortEnabled <Boolean>]
 [-ParameterSniffing <Boolean>] [-ParameterSniffingForSecondary <Boolean>] [-QueryOptimizerHotfixes <Boolean>]
 [-QueryOptimizerHotfixesForSecondary <Boolean>] [-QuotedIdentifiersEnabled <Boolean>] [-ReadOnly <Boolean>]
 [-RecursiveTriggersEnabled <Boolean>] [-RemoteDataArchiveEnabled <Boolean>]
 [-RemoteDataArchiveUseFederatedServiceAccount <Boolean>] [-TemporalHistoryRetentionEnabled <Boolean>]
 [-TransformNoiseWords <Boolean>] [-Trustworthy <Boolean>] [-ChangeTrackingRetentionPeriod <Int32>]
 [-DefaultFullTextLanguage <Int32>] [-DefaultLanguage <Int32>] [-MaxDop <Int32>] [-MaxDopForSecondary <Int32>]
 [-MirroringRedoQueueMaxSize <Int32>] [-MirroringTimeout <Int32>] [-TargetRecoveryTime <Int32>]
 [-TwoDigitYearCutoff <Int32>] [-MaxSizeInBytes <Double>] [-Collation <String>]
 [-FilestreamDirectoryName <String>] [-MirroringPartner <String>] [-MirroringPartnerInstance <String>]
 [-MirroringWitness <String>] [-PersistentVersionStoreFileGroup <String>] [-PrimaryFilePath <String>]
 [-RemoteDataArchiveCredential <String>] [-RemoteDataArchiveEndpoint <String>]
 [-RemoteDataArchiveLinkedServer <String>] [-RemoteDatabaseName <String>]
 [-ChangeTrackingRetentionPeriodUnits <RetentionPeriodUnits>] [-CompatibilityLevel <CompatibilityLevel>]
 [-ContainmentType <ContainmentType>] [-DelayedDurability <DelayedDurability>]
 [-FilestreamNonTransactedAccess <FilestreamNonTransactedAccessType>]
 [-MirroringSafetyLevel <MirroringSafetyLevel>] [-PageVerify <PageVerify>] [-RecoveryModel <RecoveryModel>]
 [-UserAccess <DatabaseUserAccess>] [-Force] [-PassThru] [-WhatIf]
 [-Confirm] [<CommonParameters>]

DESCRIPTION

This command sets properties of a database in a SQL Server Database Engine instance.

The command supports a comprehensive set of settable database properties including configuration settings, security properties, performance settings, and state information. Users can set one or multiple properties in a single command execution.

All properties correspond directly to Microsoft SQL Server Management Objects (SMO) Database class properties and support the same data types and values as the underlying SMO implementation.

EXAMPLES

EXAMPLE 1

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance'
Set-SqlDscDatabaseProperty -ServerObject $serverObject -Name 'MyDatabase' -RecoveryModel 'Simple'

Sets the recovery model of the database named MyDatabase to Simple.

EXAMPLE 2

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance'
$databaseObject = $serverObject | Get-SqlDscDatabase -Name 'MyDatabase'
Set-SqlDscDatabaseProperty -DatabaseObject $databaseObject -ReadOnly $false -AutoClose $false

Sets multiple database properties at once using a database object.

EXAMPLE 3

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance'
Set-SqlDscDatabaseProperty -ServerObject $serverObject -Name 'MyDatabase' -CompatibilityLevel 'Version160' -Trustworthy $false -Force

Sets the compatibility level and trustworthy property of the database without prompting for confirmation.

PARAMETERS

-AcceleratedRecoveryEnabled

Specifies whether Accelerated Database Recovery (ADR) is enabled for the database.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-AnsiNullDefault

Specifies whether new columns allow NULL by default unless explicitly specified (when ON).

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-AnsiNullsEnabled

Specifies whether comparisons to NULL follow ANSI SQL behavior (when ON, x = NULL yields UNKNOWN).

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-AnsiPaddingEnabled

Specifies whether padding for variable-length columns (e.g., CHAR/VARCHAR) follows ANSI rules.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-AnsiWarningsEnabled

Specifies whether ANSI warnings are generated for certain conditions (when ON, e.g., divide by zero).

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-ArithmeticAbortEnabled

Specifies whether a query is terminated when an overflow or divide-by-zero error occurs.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-AutoClose

Specifies whether the database closes after the last user exits.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-AutoCreateIncrementalStatisticsEnabled

Specifies whether creation of incremental statistics on partitioned tables is allowed.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-AutoCreateStatisticsEnabled

Specifies whether single-column statistics are automatically created for query optimization.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-AutoShrink

Specifies whether the database automatically shrinks files when free space is detected.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-AutoUpdateStatisticsAsync

Specifies whether statistics are updated asynchronously, allowing queries to proceed with old stats.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-AutoUpdateStatisticsEnabled

Specifies whether statistics are automatically updated when they are out-of-date.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-BrokerEnabled

Specifies whether Service Broker is enabled for the database.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-ChangeTrackingAutoCleanUp

Specifies whether automatic cleanup of change tracking information is enabled.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-ChangeTrackingEnabled

Specifies whether change tracking is enabled for the database.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-ChangeTrackingRetentionPeriod

Specifies the retention period value for change tracking information.

Type: Int32
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-ChangeTrackingRetentionPeriodUnits

Specifies the units for the retention period (e.g., DAYS, HOURS).

Type: RetentionPeriodUnits
Parameter Sets: (All)
Aliases:
Accepted values: None, Minutes, Hours, Days

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-CloseCursorsOnCommitEnabled

Specifies whether open cursors are closed when a transaction is committed.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-Collation

Specifies the default collation for the database.

Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-CompatibilityLevel

Specifies the database compatibility level (affects query processor behavior and features).

Type: CompatibilityLevel
Parameter Sets: (All)
Aliases:
Accepted values: Version60, Version65, Version70, Version80, Version90, Version100, Version110, Version120, Version130, Version140, Version150, Version160, Version170

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-ConcatenateNullYieldsNull

Specifies whether concatenation with NULL results in NULL (when ON).

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-ContainmentType

Specifies the containment level of the database (NONE or PARTIAL).

Type: ContainmentType
Parameter Sets: (All)
Aliases:
Accepted values: None, Partial

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-DatabaseObject

Specifies the database object to modify (from Get-SqlDscDatabase).

Type: Database
Parameter Sets: DatabaseObjectSet
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: True (ByValue)
Accept wildcard characters: False

-DatabaseOwnershipChaining

Specifies whether ownership chaining across objects within the database is enabled.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-DataRetentionEnabled

Specifies whether SQL Server data retention policy is enabled at the database level.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-DateCorrelationOptimization

Specifies whether date correlation optimization is enabled to speed up temporal joins.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-DefaultFullTextLanguage

Specifies the LCID of the default full-text language.

Type: Int32
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-DefaultLanguage

Specifies the ID of the default language for the database.

Type: Int32
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-DelayedDurability

Specifies the delayed durability setting for the database (DISABLED, ALLOWED, FORCED).

Type: DelayedDurability
Parameter Sets: (All)
Aliases:
Accepted values: Disabled, Allowed, Forced

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-EncryptionEnabled

Specifies whether Transparent Data Encryption (TDE) is enabled.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-FilestreamDirectoryName

Specifies the directory name used for FILESTREAM data.

Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-FilestreamNonTransactedAccess

Specifies the FILESTREAM access level for non-transactional access.

Type: FilestreamNonTransactedAccessType
Parameter Sets: (All)
Aliases:
Accepted values: Off, ReadOnly, Full

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-Force

Specifies that the database should be modified without any confirmation.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-HonorBrokerPriority

Specifies whether honoring Service Broker conversation priority is enabled.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-IsFullTextEnabled

Specifies whether full-text search is enabled.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-IsLedger

Specifies whether the database is a ledger database.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-IsParameterizationForced

Specifies whether forced parameterization is enabled for the database.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-IsReadCommittedSnapshotOn

Specifies whether READ_COMMITTED_SNAPSHOT isolation is ON.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-IsSqlDw

Specifies whether the database is a SQL Data Warehouse database.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-IsVarDecimalStorageFormatEnabled

Specifies whether vardecimal compression is enabled.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-LegacyCardinalityEstimation

Specifies whether the legacy cardinality estimator is enabled for the primary.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-LegacyCardinalityEstimationForSecondary

Specifies whether the legacy cardinality estimator is enabled for secondary replicas.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-LocalCursorsDefault

Specifies whether cursors are local by default instead of global (when ON).

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-MaxDop

Specifies the MAXDOP database-scoped configuration for primary replicas.

Type: Int32
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-MaxDopForSecondary

Specifies the MAXDOP database-scoped configuration for secondary replicas.

Type: Int32
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-MaxSizeInBytes

Specifies the maximum size of the database in bytes.

Type: Double
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-MirroringPartner

Specifies the mirroring partner server name (if configured).

Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-MirroringPartnerInstance

Specifies the mirroring partner instance name (if configured).

Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-MirroringRedoQueueMaxSize

Specifies the redo queue maximum size for mirroring/AGs.

Type: Int32
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-MirroringSafetyLevel

Specifies the mirroring safety level (FULL/Off/HighPerformance).

Type: MirroringSafetyLevel
Parameter Sets: (All)
Aliases:
Accepted values: None, Unknown, Off, Full

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-MirroringTimeout

Specifies the timeout in seconds for mirroring sessions.

Type: Int32
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-MirroringWitness

Specifies the mirroring witness server (if used).

Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-Name

Specifies the name of the database to modify.

Type: String
Parameter Sets: ServerObjectSet
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-NestedTriggersEnabled

Specifies whether triggers are allowed to fire other triggers (nested triggers).

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-NumericRoundAbortEnabled

Specifies whether an error is raised on loss of precision due to rounding (when ON).

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-PageVerify

Specifies the page verification setting (NONE, TORN_PAGE_DETECTION, CHECKSUM).

Type: PageVerify
Parameter Sets: (All)
Aliases:
Accepted values: None, TornPageDetection, Checksum

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-ParameterSniffing

Specifies whether parameter sniffing behavior is enabled on the primary.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-ParameterSniffingForSecondary

Specifies whether parameter sniffing is enabled on secondary replicas.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-PassThru

Specifies that the database object should be returned after modification.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-PersistentVersionStoreFileGroup

Specifies the filegroup used for the Persistent Version Store (PVS).

Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-PrimaryFilePath

Specifies the path of the primary data files directory.

Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-QueryOptimizerHotfixes

Specifies whether query optimizer hotfixes are enabled on the primary.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-QueryOptimizerHotfixesForSecondary

Specifies whether query optimizer hotfixes are enabled on secondary replicas.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-QuotedIdentifiersEnabled

Specifies whether identifiers can be delimited by double quotes (when ON).

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-ReadOnly

Specifies whether the database is in read-only mode.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-RecoveryModel

Specifies the database recovery model (FULL, BULK_LOGGED, SIMPLE).

Type: RecoveryModel
Parameter Sets: (All)
Aliases:
Accepted values: Full, BulkLogged, Simple

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-RecursiveTriggersEnabled

Specifies whether a trigger is allowed to fire itself recursively.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-Refresh

Specifies that the ServerObject's databases should be refreshed before trying to get the database object. This is helpful when databases could have been modified outside of the ServerObject, for example through T-SQL. But on instances with a large amount of databases it might be better to make sure the ServerObject is recent enough.

This parameter is only used when setting properties using ServerObject and Name parameters.

Type: SwitchParameter
Parameter Sets: ServerObjectSet
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-RemoteDataArchiveCredential

Specifies the credential name for Stretch Database/remote data archive.

Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-RemoteDataArchiveEnabled

Specifies whether Stretch Database (remote data archive) is enabled.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-RemoteDataArchiveEndpoint

Specifies the endpoint URL for remote data archive.

Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-RemoteDataArchiveLinkedServer

Specifies the linked server used by remote data archive.

Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-RemoteDataArchiveUseFederatedServiceAccount

Specifies whether to use federated service account for remote data archive.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-RemoteDatabaseName

Specifies the remote database name for remote data archive.

Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-ServerObject

Specifies current server connection object.

Type: Server
Parameter Sets: ServerObjectSet
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-TargetRecoveryTime

Specifies the target recovery time (seconds) for indirect checkpointing.

Type: Int32
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-TemporalHistoryRetentionEnabled

Specifies whether automatic cleanup of system-versioned temporal history is enabled.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-TransformNoiseWords

Specifies how full-text noise word behavior is controlled during queries.

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-Trustworthy

Specifies whether implicit access to external resources by modules is allowed (use with caution).

Type: Boolean
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-TwoDigitYearCutoff

Specifies the two-digit year cutoff used for date conversion.

Type: Int32
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-UserAccess

Specifies the database user access mode (MULTI_USER, RESTRICTED_USER, SINGLE_USER).

Type: DatabaseUserAccess
Parameter Sets: (All)
Aliases:
Accepted values: Multiple, Single, Restricted

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-Confirm

Prompts you for confirmation before running the cmdlet.

Type: SwitchParameter
Parameter Sets: (All)
Aliases: cf

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-WhatIf

Shows what would happen if the cmdlet runs. The cmdlet is not run.

Type: SwitchParameter
Parameter Sets: (All)
Aliases: wi

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

CommonParameters

This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.

INPUTS

`[Microsoft.SqlServer.Management.Smo.Database]`

The database object to modify (from Get-SqlDscDatabase).

OUTPUTS

None.

When PassThru is not specified, no output is returned.

Microsoft.SqlServer.Management.Smo.Database

When PassThru is specified, returns the updated database object.

NOTES

The following database properties are read-only after creation and cannot be modified using this command:

CatalogCollation: The catalog-level collation used for metadata and temporary objects. This property is marked as ReadOnlyAfterCreation in the SMO Database class and can only be set during database creation (e.g., using New-SqlDscDatabase or CREATE DATABASE statements).
DatabaseSnapshotBaseName: The base name of the source database for a database snapshot. This property is marked as ReadOnlyAfterCreation in the SMO Database class. To create database snapshots, use the New-SqlDscDatabaseSnapshot or New-SqlDscDatabase command with the -DatabaseSnapshotBaseName parameter.

There are some database properties that require method calls instead of direct property assignment and will be supported through separate commands, e.g. Set-SqlDscDatabaseDefaultFileGroup.

Azure SQL Database service tier and service objective changes should be managed using Azure management cmdlets (e.g., Set-AzSqlDatabase with -Edition and -RequestedServiceObjectiveName parameters) rather than through SMO.