Move SharePoint Database

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:

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.

The next step is to use SQL connection aliases to update all Web servers. To do this, open the SQL Server Configuration Manager.

SQL Server Configuration Manager

From the application, expand the SQL Native Client Configuration node and from the Aliases node, right click and select New Alias…

New Alias

For the new alias, ensure you enter the source server instance name as the Alias Name, select TCP/IP and enter the new server name as the Server.

New Alias Details

Of course, you may not have SQL Server Configuration Manager installed on your WFE and application servers. In this case, the following method will create a new SQL alias for you.

Run the program cliconfg.exe located by default in C:\Windows\System32.

Alias Tab

Switch to the Alias tab, as shown above and click the Add button. Fill in the alias information in the same way as described above, then click OK twice to apply the alias.

Save Server Alias

Don’t know what port SQL is listening on? Go to your SQL Server and open the SQL Server Configuration Manager application. Click on the SQL Server Services node in the left hand tree and locate the instance being used by SharePoint. Take a note of the Process ID for it.

SQL Process ID

Now open a command prompt and type the following: netstat -ano | find /i “1508”

Replace the value 1508 with the PID from your SQL Server instance, keeping the double quotes. From the output, you’ll see the port that’s being used.

netstat Output

Repeat this process on all Web and Application servers in the farm.

The last step in this process is to restart IIS and the services that you stopped previously. This involves doing the following:

Type the following at a command prompt: iisreset /start and then start the following Windows services.

• 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

All going well, your SharePoint sites will come back up and will be using the new database location.