Big Data, Data Analytics and Data Science | BI-BIGDATA.com

This article will help to understand how to alter databases properties using PowerShell V3.0 and SMO. In previous article we seen PowerShell for Creating a Database. Basically we can alter database options from Automatic, Containment, Cursor, File Stream, Miscellaneous, Recovery, Service Broker and State Categories.

Following PowerShell Script will help you to understand what I’m talking about.

#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#your SQL Server Instance Name
$SQLInstanceName = "SQL2012"
$Server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SQLInstanceName

#provide your database name where you want to change database properties
$DatabaseName = "PSDB"

#create SMO handle to your database
$DBObject = $Server.Databases[$DatabaseName]

#Database Options to change ANSI PADDING, NULLS and Warnings
$DBObject.DatabaseOptions.AnsiPaddingEnabled = $false
$DBObject.DatabaseOptions.AnsiNullsEnabled = $false
$DBObject.DatabaseOptions.AnsiWarningsEnabled = $false

#Database Options to change Database User Access
#DatabaseUserAccess enum :: single, multiple, restricted
$DBObject.DatabaseOptions.UserAccess = [Microsoft.SqlServer.Management.Smo.DatabaseUserAccess]:: Restricted
$DBObject.Alter()

#In some cases you cannot use DatabaseOptions
#where you can update your SMO handle directly
$DBObject.CompatibilityLevel = [Microsoft.SqlServer.Management.Smo.CompatibilityLevel]::Version90
$DBObject.AutoUpdateStatisticsEnabled =$true
$DBObject.Alter()

#Set your database ReadOnly with DatabaseOptions
$DBObject.DatabaseOptions.ReadOnly = $true
$DBObject.Alter()

Following screen show is talking about the Updated Database Options during the execution of above PowerShell Script. You can browse this screen by selecting database properties and Options tab where you can notice updated Database Options during PowerShell Execution.

Hope this Helps

Sandip