How to Set SharePoint Content Database to Read Only Mode using PowerShell?

Requirement: Set content database to read only in SharePoint Server using PowerShell.

Solution:  We may have to set SharePoint databases to read-only mode in scenarios such as upgrades, disaster recovery / highly available maintenance, patching, etc. Setting SharePoint content database in read-only mode from SQL Server Management Studio is explained in another article: How to make SharePoint database read only?. This time, let’s make the content database read only using PowerShell.

Tips: Use Get-SPContentDatabase | Select Name to get all SharePoint content databases!

PowerShell to set content database read only SharePoint

Prerequisites: Make sure you have SQL Server Management Studio installed in your server/workstation to use the below script!

#You may have to adjust the below path according to your SSMS version!
Add-Type -Path "C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

#Configuration variables 
$SQLInstance ="Abjsvrv9"
$DatabaseName = "SP16_Content_Crescent_Intranet"

Try {
    #Set the Error Action
    $ErrorActionPreference = "Stop"

    #Get the Server
    $Server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SQLInstance
      
    #Get the Database
    $Database = $Server.Databases[$DatabaseName]

    $Server.KillAllProcesses($DatabaseName)
    #Set Read only option to True
    $Database.DatabaseOptions.ReadOnly = $true
    $Database.Alter()
    
    Write-host "$($DatabaseName) Database made Read-only" -f Green
}
catch {
 Write-Host "Operation Failed. Find the Error Message below:" -ForegroundColor Red
 Write-Host $_.Exception.Message -ForegroundColor Red
}
finally {
 #Reset the Error Action to Default
 $ErrorActionPreference = "Continue"
}

This makes read only content database in SharePoint. To verify, You can navigate to SharePoint 2013 central Administration >> Application Management >> Manage Content Databases >> Check the “Database Read-Only” flag!

sharepoint content database read only

If you navigate to any of the site collection which lives in the above content database, you’ll find the maintenance mode notification in SharePoint 2016 sites!

sharepoint 2013 read only content database

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

Leave a Reply

Your email address will not be published.