commands

^

Set-DbaDbState

Author Simone Bizzotto (@niphold)
Availability Windows, Linux, macOS

 

Synopsis

Sets various options for databases, hereby called "states"

Description

Sets some common "states" on databases:

  • "RW" options (ReadOnly, ReadWrite)
  • "Status" options (Online, Offline, Emergency, plus a special "Detached")
  • "Access" options (SingleUser, RestrictedUser, MultiUser)

Returns an object with SqlInstance, Database, RW, Status, Access, Notes

Notes gets filled when something went wrong setting the state

Syntax

Set-DbaDbState
    [-SqlCredential <PSCredential>]
    [-Database <Object[]>]
    [-ExcludeDatabase <Object[]>]
    [-AllDatabases]
    [-ReadOnly]
    [-ReadWrite]
    [-Online]
    [-Offline]
    [-Emergency]
    [-Detached]
    [-SingleUser]
    [-RestrictedUser]
    [-MultiUser]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Set-DbaDbState -SqlInstance <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>]
    [-Database <Object[]>]
    [-ExcludeDatabase <Object[]>]
    [-AllDatabases]
    [-ReadOnly]
    [-ReadWrite]
    [-Online]
    [-Offline]
    [-Emergency]
    [-Detached]
    [-SingleUser]
    [-RestrictedUser]
    [-MultiUser]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Set-DbaDbState
    [-SqlCredential <PSCredential>]
    [-Database <Object[]>]
    [-ExcludeDatabase <Object[]>]
    [-AllDatabases]
    [-ReadOnly]
    [-ReadWrite]
    [-Online]
    [-Offline]
    [-Emergency]
    [-Detached]
    [-SingleUser]
    [-RestrictedUser]
    [-MultiUser]
    [-Force]
    [-EnableException] -InputObject <PSObject[]>
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> Set-DbaDbState -SqlInstance sqlserver2014a -Database HR -Offline

Sets the HR database as OFFLINE

Example: 2
PS C:\> Set-DbaDbState -SqlInstance sqlserver2014a -AllDatabases -Exclude HR -ReadOnly -Force

Sets all databases of the sqlserver2014a instance, except for HR, as READ_ONLY

Example: 3
PS C:\> Get-DbaDbState -SqlInstance sql2016 | Where-Object Status -eq 'Offline' | Set-DbaDbState -Online

Finds all offline databases and sets them to online

Example: 4
PS C:\> Set-DbaDbState -SqlInstance sqlserver2014a -Database HR -SingleUser

Sets the HR database as SINGLE_USER

Example: 5
PS C:\> Set-DbaDbState -SqlInstance sqlserver2014a -Database HR -SingleUser -Force

Sets the HR database as SINGLE_USER, dropping all other connections (and rolling back open transactions)

Example: 6
PS C:\> Get-DbaDatabase -SqlInstance sqlserver2014a -Database HR | Set-DbaDbState -SingleUser -Force

Gets the databases from Get-DbaDatabase, and sets them as SINGLE_USER, dropping all other connections (and rolling back open transactions)

Required Parameters

-SqlInstance

The target SQL Server instance or instances

Alias
Required True
Pipeline true (ByPropertyName)
Default Value
-InputObject

Accepts piped database objects

Alias
Required True
Pipeline true (ByValue)
Default Value

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.

Alias
Required False
Pipeline false
Default Value
-Database

The database(s) to process - this list is auto-populated from the server. if unspecified, all databases will be processed.

Alias
Required False
Pipeline false
Default Value
-ExcludeDatabase

The database(s) to exclude - this list is auto-populated from the server

Alias
Required False
Pipeline false
Default Value
-AllDatabases

This is a parameter that was included for safety, so you don't accidentally set options on all databases without specifying

Alias
Required False
Pipeline false
Default Value False
-ReadOnly

RW Option : Sets the database as READ_ONLY

Alias
Required False
Pipeline false
Default Value False
-ReadWrite

RW Option : Sets the database as READ_WRITE

Alias
Required False
Pipeline false
Default Value False
-Online

Status Option : Sets the database as ONLINE

Alias
Required False
Pipeline false
Default Value False
-Offline

Status Option : Sets the database as OFFLINE

Alias
Required False
Pipeline false
Default Value False
-Emergency

Status Option : Sets the database as EMERGENCY

Alias
Required False
Pipeline false
Default Value False
-Detached

Status Option : Detaches the database

Alias
Required False
Pipeline false
Default Value False
-SingleUser

Access Option : Sets the database as SINGLE_USER

Alias
Required False
Pipeline false
Default Value False
-RestrictedUser

Access Option : Sets the database as RESTRICTED_USER

Alias
Required False
Pipeline false
Default Value False
-MultiUser

Access Option : Sets the database as MULTI_USER

Alias
Required False
Pipeline false
Default Value False
-Force

For most options, this translates to instantly rolling back any open transactions that may be stopping the process. For -Detached it is required to break mirroring and Availability Groups

Alias
Required False
Pipeline false
Default Value False
-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.

Alias
Required False
Pipeline false
Default Value False
-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

Alias wi
Required False
Pipeline false
Default Value
-Confirm

Prompts you for confirmation before executing any changing operations within the command.

Alias cf
Required False
Pipeline false
Default Value

 

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