Home > Uncategorized > Set SQL 2012 DBs to Read Only using PowerShell

Set SQL 2012 DBs to Read Only using PowerShell

I needed to set all my SharePoint 2010 Content DBs to Read-Only within SQL for my DR COOP environment but I did not want to have to go to each DBs Properties-Options-ReadOnly setting since I have quite a few SharePoint Content DBs so I wrote a PowerShell script to help me out.

For those that are interested on how I accomplish this.

—————————————————————–

#Load SQL Server SMO assemblies
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.Smo’) | out-null
#Make Connection to SQL Server/Instance
$sqlServer = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “NAME OF SQL SERVER/INSTANCE”
#Grab all user DBs
$dbs = $sqlServer.databases

#Foreach loop to loop through all user dbs attached in SQL
foreach ($db in $dbs)
{
#If statement to search for only user dbs with SPContent as part of its name, and exclude the CA database.  Here you would substitute SPContent for the naming convention of your DBs.
if ($db.Name -like “*SPContent_*” -and $db.Name -notlike “*SPContent_Admin*”)
{
$dbname = $db.Name
$DBObject = $sqlServer.Databases[$dbname]
#Kill all the DBs processes to allow for database alter
Write-Host “Killing $dbname Proccess”
$sqlServer.KillAllProcesses($dbname)
#Set db object to ReadOnly set flag as $false; if you want to revert back set flag to $true (read/write)
Write-Host “Setting $dbname to Read-Only”
$DBObject.DatabaseOptions.ReadOnly = $false
#Alter DB Object
$DBObject.Alter()
}
}
Write-Host “Done’

——————————————————————

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: