Backup-Restore All SharePoint Databases in SQL Server using PowerShell

Requirement: We planned to move our SharePoint’s SQL database to a different server. So, we had to backup and restore all SharePoint databases from one SQL Server 2012 instance to another.

How to Backup and Restore All SharePoint Databases from SQL Server using PowerShell?

Here are the steps at a high level to move SharePoint from one SQL Server to another:

  1. Get a list of all SharePoint databases
  2. Backup all SharePoint databases from the source SQL Server
  3. Restore the backups to the target SQL server instance
  4. Change the SQL Alias in SharePoint Server to point to the new server.

Step 1: Get all SharePoint databases

#Get All SharePoint Databases and form an Array
Get-SPDatabase | Select -ExpandProperty Name | foreach { $Databases+= $_+"," }

#Export to a txt file
$Databases > databases.txt

This script gives you a list of all SharePoint databases, including configuration database, content databases, and service application databases to a comma-separated string. Let’s take that string and give it to our backup script.

Tips: If you want to get all Content databases, use: Get-SPContentDatabase | Select -Expandproperty Name

Step 2: Backup All SharePoint Databases:

Before taking backup of all SharePoint databases, make sure you set the site(s) to Read-only mode (or set the database to Read-only in SQL Server) in order to avoid any data inconsistency issues.

PowerShell script to backup all SharePoint Databases from SQL Server:

#Import SQL PowerShell module
Import-Module SQLPS -DisableNameChecking

#Configuration parameters
$SQLInstance="Cre-SQL-001"
$BackupPath="F:\Backup\"

#Array of Database names, Comma separated
$DatabaseColl="StateService_d73ac2ad313a41ba83d457bfb78cb4f3,Secure_Store_Service_DB_c13a71870bad4decb6f7582d9e26f46a,WordAutomationServices_e290fc12c8ee4ce09c38ba7fc8f089c9,Social DB,Bdc_Service_DB_f937be05fe5f48158d4895b6f2bb9674,WebAnalyticsServiceApplication_StagingDB_9efa01f9-ce98-4e4e-9e2d-ecfc9e19fc79,SharePoint_Config_2010,PerformancePoint Service Application_c1a8062656434a24a9c53f05fc8144f7,Search_Service_Application_CrawlStoreDB_76166d368e4a4ef58aabf8df57d7c670,Search_Service_Application_PropertyStoreDB_1fc75775ee894fd2aa0c4d1e7c75f0c7,Application_Registry_Service_DB_8e93a305ea904c81a34a3dd205265fc7,SharePoint_AdminContent_e0021dbb-cc00-4b58-a16d-9c4c481228ff,WSS_Content,WSS_Content_KM,WSS_Content_MySites,Search_Service_Application_DB_bfdf7354e7f84b1281121c7bdca7b43f,Managed Metadata Service_ff52c4f0cd504d938cd1b3dc9ef9ae8b,Profile DB,WSS_Logging,Sync DB,WebAnalyticsServiceApplication_ReportingDB_894b25ce-a2ba-4c7c-885e-08b3924deec8"

#Convert comma separated into an Array
$DatabaseNames = $DatabaseColl -split ","

#Backup Each Database
Foreach($DBName in $DatabaseNames)
{
  write-host "Backing up the database: $($DBName)"
  Backup-SqlDatabase -ServerInstance $SQLInstance -Database $DBName -BackupFile "$($BackupPath)\$($DBName).bak"
}

Step 3: Restore all databases to the target SQL Server instance

Copy all backup files from the source server to the target, and then use the below PowerShell script to restore SQL Server databases in bulk. Ensure the original location (as in the source database server) exists in the target server.

PowerShell script to restore SQL Server databases in bulk:

#Import SQL PowerShell module
Import-Module SQLPS -DisableNameChecking

#Configuration parameters
$SQLInstanceName="Cre-SQL-002"
$BackupPath="E:\Backup\"

#Get all Backup files
$BackupFilesColl = Get-ChildItem -Path $BackupPath | where {$_.extension -eq ".bak"}

#Iterate through each backup file
Foreach($BackupFile in $BackupFilesColl)
{
 #Get the full path of the backup file
        $DatabasePath = $BackupPath + '\' + $BackupFile

 #Frame the Database Name
        $DatabaseName = $BackupFile.ToString().Replace(".bak", "")

 #Restore the database
 write-host "Restoring Database: $($DatabaseName)"
 Restore-SqlDatabase -ServerInstance $SQLInstanceName -Database $DatabaseName -BackupFile $DatabasePath
}
Backup-Restore All SharePoint Databases in SQL Server using PowerShell

Step 4: Change SQL Server Alias to Point your New SQL Server:

As a final step, change your SQL Alias from SharePoint servers to point to the new SQL server instance. Here is my post on configuring SQL server alias: How to Create SQL Server Alias for SharePoint. Do IISReset once done!

These scripts can be used in scenarios, such as:

  • During SharePoint migrations, where you may be taking all SharePoint databases to a new SQL Server instance.
  • When you have to move SharePoint databases from a Single standalone SQL Server to a highly available cluster, or SQL Server AlwaysOn cluster.

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

3 thoughts on “Backup-Restore All SharePoint Databases in SQL Server using PowerShell

  • Can I recovery a sharepoint Server (Project Server) using only DB Backups (BK Files) ?

    Reply
    • Yes – but only the data. Other stuff like customizations, solutions go to the file system of servers. So, you have to redeploy them.

      Reply
  • Hey rajack your articles are too good. I have a question, have you ever worked on Microsoft DPM. if yes could be able to provide the guidelines of how to work on that. Next I would like to know the best practices of DR.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *