Requirement: Move SharePoint databases to a new SQL Server! You’ll have to move SharePoint databases including all content databases, configuration databases, and service application databases to another SQL Server at least in a couple of circumstances:
- Upgrading SQL Server to a new version. E.g. Upgrading SQL Server 2012 to SQL Server 2016.
- Upgrading Servers to new hardware, moving servers from data centers, etc.
- Moving from single standalone server instances to highly available cluster servers – AlwaysOn
In a nutshell, Moving SharePoint database from one server to another can be achieved with the following steps:
- Step 1: Stop All SharePoint Services:
- Step 2: Backup & Restore All SharePoint Databases to the new SQL Server
- Step 3: Change the SQL Alias to Point to the new SQL Server
- Step 4: Start All SharePoint Services
Step 1: Stop All SharePoint Services:
To start with moving SharePoint databases to a new server, stop all SharePoint-related services on all SharePoint servers of the farm So that no connections are made to the SQL server during the migration. Use this PowerShell script to stop all SharePoint
2013 services: How to Stop all SharePoint 2013 Services using PowerShell
Step 2: Backup & Restore All SharePoint Databases to the new SQL Server
Use the PowerShell cmdlet “Get-SPDatabase” to get all SharePoint databases and make a note of them. Proceed with backup each database.
Backup SharePoint Databases in SQL Server:
- Go to SQL Server Management Studio >> Right click on the relevant SharePoint database >> Tasks > Back Up.
- Provide necessary inputs to the backup dialog window and complete backup for all SharePoint databases.
Restore SharePoint Database backups to the New database server:
You have to make sure your Farm access account & service accounts have access to the target SQL Server! So before restore, transfer all Logins from the old server to a new server: https://support.microsoft.com/en-us/kb/918992. This is important as the user access mappings are within the backup we made and should be restored.
- I’m assuming SQL Server is installed on your new SQL Server, log in to your new SQL Server Database Server, transfer all Backup files from Old SQL Server to your new SQL Server.
- Go to SQL Server management studio >> Right-click on Databases node > Restore Database >> Pick the database backup. Repeat this step for all database backups.
Other than backup-restore, You can also use the copy database wizard to copy databases from one SQL server to another. Right-click the source database from SSMS >> Tasks >> Copy Database >> Run through the Wizard to copy a database between SQL Servers. Alternatively, you can detach and attach to move SQL Databases between servers.
Step 3: Create/Change the SQL Alias to Point to the new SQL Server
Once SharePoint databases are restored into the target SQL server, the next step is to create or change SQL Server Alias on All SharePoint Servers of the Farm. Basically, we will redirect from the original SQL server to the new SQL server.
- Open the cliconfg.exe tool >> Under Alias tab, add a new alias or change existing alias for your SharePoint farm (If your SharePoint farm uses SQL Alias already).
- In my case: My old SQL Server is ‘AzSvrV18’ and I want to move all Databases to ‘SP16-SQL’. After restoring all SharePoint Databases on SP16-SQL, I created an alias on all SharePoint servers to make ‘AzSvrV18’ point to ‘SP16-SQL’.
Here, we have created an alias for the new SQL instance and gave this alias the same name as the current instance of SQL Server that the SharePoint databases are hosted. Please note, alias can only be configured after the original SQL instance is stopped or Turned OFF to avoid any conflicts. Refer this article to configure SQL alias for SharePoint: How to Create SQL Server Alias for SharePoint
Step 4: Start All SharePoint Services
Start all the services that we stopped. Use the article at Step-1 to start all SharePoint services.
That’s it! We have moved SharePoint database to new server. Pretty straight forward, huh! Next time you want to change your Database server, Just update the Server name in SQL Alias! No need to run SharePoint configuration wizard and disconnect from farm and so on! You can use this approach for other requirements such as: move SharePoint database from express to SQL server, move SharePoint database from express to standard. Technet article on moving SharePoint 2013 databases: https://technet.microsoft.com/en-us/library/cc512725.aspx
Related post on moving SharePoint content databases to another drive: Move SharePoint 2013 database to another drive