Move all SharePoint databases from one SQL server to another SQL server

This article is not new article for moving SharePoint databases to another SQL server. Lot’s of SharePoint experts has written such articles. But, this article covers real time experience point to point.

I had situation where client wanted to move all SharePoint databases from one SQL server which was running on SQL 2014 to another server running on SQL 2016. Process is very simple.

1. First get the list of SharePoint databases using Get-SPDatabases powershell. And put down in notepad
2. All the databases from the above list, put offline from SQL Management Studio
3. Go to the SQL folder and copy MDF and LDF files for all above listed databases. At this stage you are done with old SQL server.
4. Paste in new SQL server working folder
5. Now its time to attach the database on new SQL server. Wait, as I am moving from SQL 2014 to SQL 2016 which needs upgrade the database before attaching through SQL Management studio
6. EXEC sp_attach_db @dbname = N'<DATABASE NAME>’,
   @filename1 =
   N'<MDF DATABASE FILE PATH>’,
   @filename2 =
   N'<LDF DATABASE FILE PATH>’;
7. This command with automatically upgrade your database. Repeat this step for all the databases
8. At this stage, new SQL server is ready with all SharePoint databases.
9. Let’s ask SharePoint to go to new SQL server as its address is changed now. This process is called “Set SQL alias”. Run this “C:\windows\system32\cliconfg.exe”. Refer this link about how to set alias. You need to do this for all the SP servers.
10. You are done.

 

Here is one catch in setting the alias. From the SharePoint point of view, there would be no problem in SharePoint. But, if any of your other application is referring the SQL server and you have set connection string by Name of SQL server, it will be redirected to new server instead of OLD server. It means, setting alias is machine wise not the application wise.

Enjoy!