Moving SharePoint Database from One Server to another Server

Moving SharePoint Database is different from other databases.

DATABASE SIDE STEPS

First thing first, 

1. Before you plan to schedule this operation please make sure to have everyone logged out of the window and give your self few hours to complete this operation (depending on the size of the database to move from one server to another).
2. Make sure new Database server has same specs as existing database server, eg SQL version, 
3. Log on to existing database server and Take a full backup of each database.

My server has databases like

• Central Administration content database
• Farm Configuration database
• State Service Database
• Content database
• Search service application database
• Managed metadata service database
• User Profile service application database
• Usage and Health Data Collection

• Move database backups from existing server to new desired server
• Restore all the Backups to this new Database server.
• After restoring all databases, Compare size of database between the source and destination to make sure you successfully perform a restore.

SHAREPOINT SIDE STEPS

Stop all SharePoint and IIS Related Services

• SharePoint 2010 Administration
• SharePoint 2010 Timer
• SharePoint 2010 Tracing
• SharePoint 2010 User Code Host
• SharePoint 2010 VSS Writer
• SharePoint Foundation Search V4
• World Wide Web Publishing Service
• SharePoint Server Search 14
• Web Analytics Data Processing Service
• Web Analytics Web Service

Stop IIS

At the command prompt under admin context, type iisreset /stop

Verify what ports your New SQL is usingIf it’s using a dynamic port change it to static. To do this go to SQL Server Configuration Manager and into theSQL Server Network Configuration Section choose the instance you will be using. Go to the TCP/IP section and ifTCP Dynamic Ports are set to 0 (zero) then remove that and assign a static port into the TCP Port Section on all IP’s. For this sample we will be using 14330 <- notice I just added 0 at the end from the default port. Note that as we will need that on the next section also if you changed the port number from dynamic to static you will need to restart your SQL Server before it can take effect.

Go to your SharePoint server and create your SQL Server AliasIf SQL Configuration Manager is installed just head over to the SQL Native Client 10.0 configuration then into Aliases. Create a new alias that points to your new SQL Server by right clicking on the Alias. You need to do this on both 32 and 64 bit instances.

On the Alias name put your old SQL Server IP or NameOn the Port the port you indicated on step 7, in this case 14330Protocol should be TCP/IPAnd on Server the new SQL Server IP or Name together with its instance name if there is any. In our case it’s SQL2012

If you don’t have SQL Configuration Manager in your SharePoint Server then run the CLICONFIG again on both 32 and 64 bit instances. For 32 bit go to run command and execute this “%SystemRoot%/SysWow64/CliConfg.exe” and for 64 just this “CLICONFG” all without quotes of course. Add the same info as above.

Start all your SharePoint services, now you have successfully migrated your database.  

·         SharePoint 2010 Administration
·         SharePoint 2010 Timer
·         SharePoint 2010 Tracing
·         SharePoint 2010 User Code Host
·         SharePoint 2010 VSS Writer
·         SharePoint Foundation Search V4
·         World Wide Web Publishing Service
·         SharePoint Server Search 14
·         Web Analytics Data Processing Service
·         Web Analytics Web Service
At the command prompt under admin context, type iisreset /start.

HAPPY MOVING