commands

Clear

Set-DbaDbState


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

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.

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