dbatools is a free PowerShell module with over 300 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Set-DbaDatabaseState

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, Warning

Warning gets filled when something went wrong setting the state

Screenshots

Examples

To set the HR database as OFFLINE

Set-DbaDatabaseState -SqlServer sqlserver2014a -Database HR -Offline

To set the HR database as SINGLE_USER

Set-DbaDatabaseState -SqlServer sqlserver2014a -Database HR -SingleUser

To set the HR database as SINGLE_USER and drop all other connections (and rolling back open transactions)

Set-DbaDatabaseState -SqlServer sqlserver2014a -Database HR -SingleUser -Force

To set all user databases of the sqlserver2014a instance, except for HR to READ_ONLY

Set-DbaDatabaseState -SqlServer sqlserver2014a -AllDatabases -Exclude HR -Readonly -Force

Author

This command was created by Simone Bizzotto. You can find Simone on LinkedIn.

Get-Help

From PowerShell, execute Get-Help Set-DbaDatabaseState -Detailed for more information on this function.

Source Code

Want to see the source code? View Set-DbaDatabaseState.ps1 on GitHub
 

Related commands