Migrating SharePoint database instance to another server

Before attempting to move the databases associated with your SharePoint instance, make sure you backup your farm before continuing. For example, the following PowerShell script performs a full backup of the farm:
Backup-SPFarm -Directory c:\backuppath -BackupMethod Full
Depending on the size of the databases, this may take some time to complete.
One thing to check before starting the move is that the version of SQL is identical for both the source and target servers.
Once your happy that SharePoint has been backed up, the first thing to do is stop all SharePoint and Internet Information Services (IIS) services on the server that is running the Central Administration Web site. Do this by opening the Services snap-in from the Microsoft Management Console.

Services Snap-In

The following SharePoint services should be stopped:

• SharePoint 2010 Administration
• SharePoint 2010 Timer
• SharePoint 2010 Tracing
• SharePoint 2010 User Code Host
• SharePoint 2010 VSS Writer
• SharePoint 2010 Foundation Search V4
• SharePoint 2010 Server Search 14

SharePoint Services

Finally, stop the World Wide Web Publishing Service:

Web Service

Next, type the following at a command prompt: iisreset /stop
This will stop all running Internet Services on the server.
Moving onto the database server, open the Microsoft SQL Server Management Studio and detach the SharePoint databases.
By default, the following databases should have been created:

• Application_Registry_Service_DB_GUID
• Bdc_Service_DB_GUID
• Managed Metadata Service_GUID
• PerformancePoint Service Application_GUID
• Search_Service_Application_CrawlStoreDB_GUID
• Search_Service_Application_DB
• Search_Service_Application_PropertyStoreDB_GUID
• Secure_Store_Service_DB_GUID
• SharePoint_AdminContent_GUID
• SharePoint_Config
• StateService_GUID
• User Profile Service Application_ProfileDB_GUID
• User Profile Service Application_SocialDB_GUID
• User Profile Service Application_SyncDB_GUID
• WebAnalyticsServiceApplication_ReportingDB_GUID
• WebAnalyticsServiceApplication_StagingDB_GUID
• WordAutomationServices_GUID
• WSS_Content
• WSS_Logging

To detach, right click each database and from the Tasks menu and click Detach…

Detach Database

From the resulting dialog, click the OK button to detach the database. You could, of course, write a SQL script to do all of this for you.
Once all the relevant databases have been detached, the next step is to copy\move the mdf, log and ndf files to the new SQL server. The location of these files can easily be found by highlighting one of them (before you detached it) and looking at the Primary File Path value in the Object Explorer Details window, as shown below.

Primary File Path

Once you’ve copied or moved the database files to the new server, the next step is to ensure that all of the SQL Server logins, fixed server roles, fixed database roles, and permissions for the databases from the source server are configured correctly on the new server.
The following page from the Microsoft site describes the scripts that are required to achieve this:
To attach the databases to the new server, connect to it via the SQL Server Management Studio, right click on the Databases node in the explorer window and select Attach…

Attach Database

Next, click the Add button from the Attach Database dialog.

Add Database

Then select a database file to attach from the list presented and click the OK button.

Select Database

Finally, click the OK button in the Attach Databases dialog to complete the attach process.
Repeat this for all databases that were detached and copied to the new SQL server.

Pointing SharePoint to a new alias

It it configured at client side to expect a server can connect to a SQL server that has the same alias name. To know the SQL alias your SharePoint server specifies to, open Central Administration and then click Application Management. Hit Specify the default database server under Databases section.

You might get surprised when seeing under Database server is not the name of your database server or named instance. I’m sure that if you are not familiar with SQL alias. In fact, the name of database server in my case is SVSQL5\SPDEV. Svsqldevsp is the name of alias that is configured to facilitate SQL Server client connection.
As recommended by Todd Klindt, you should have to stop the following services using net stop <service name> command

• World Wide Web Publishing Service service (w3svc)
• SharePoint 2010 Timer service (sptimerv4)
• SharePoint 2010 Administration service (spadminv4)
• SharePoint 2010 Tracing service (sptracev4)
• SharePoint Server Search 14 (osearch14)

Alias 32 Bits: On SharePoint server, open SQL Server Client Network Utility by opening Run and type cliconfg.exe (full path C:\Windows\System32\cliconfg.exe). Click Alias tab and edit the existing alias.

Alias 64 Bits: On SharePoint server, open SQL Server Client Network Utility by opening Run and type cliconfg.exe (full path c:\windows\SysWOW64\cliconfg.exe). Click Alias tab and edit the existing alias.


Keep Server alias value by default and change Server name value to the new destination, in my case it’s SVSQL6. Do the same steps on all SharePoint application servers you have.
On the destination server, do the same steps but you have to type Server alias value.
Using net start <service_name> and iisreset to complete a reset for a whole SharePoint application server.
After restarting, I tried to open Central Administration web application and then it was working well but there was a very vargue commonly error when opening one of my site collections.