Move All SharePoint Foundation 2010 Databases for Windows SBS 2011 Standard to Another Server

Published: January 28, 2011

Applies To: Windows Small Business Server 2011 Premium Add-on, Windows Small Business Server 2011 Standard

This article describes how to move the databases that are associated with Microsoft SharePoint Foundation 2010 from a computer running Windows SBS 2011 Standard to another server. If your databases are hosted on different servers, this procedure applies to the database server that hosts the configuration database.

Important

Moving databases makes all farm sites and assets unavailable to users until the process is completed.

If hosted on a single database server, the following types of databases can be moved by using the procedures in this article:

  • Configuration database

  • Central Administration content database

  • Content databases

Terms and definitions

Source Server: The existing server from which you are moving your settings and data.

Destination Server: The new server to which you are moving your settings and data.

Primary Server: The first server on a Windows SBS 2011 Standard network that contains more than one server.

Second Server: The second server on a Windows SBS 2011 Standard network. The Second Server runs the Windows SBS 2011 Premium Add-on.

Steps to move the SharePoint Foundation 2010 databases to another server

The process of moving all of the databases from one database server to another database server requires that you perform tasks from within SharePoint Foundation 2010 Central Administration and SQL Server Management Studio. In addition, several procedure steps require that you open a Command Prompt window as an Administrator.

To open a Command Prompt window as an Administrator

  1. Click Start.

  2. In the search box, type cmd.

  3. In the list of results, right-click cmd, and then click Run as administrator.

The following list summarizes the steps that you must take to move the SharePoint Foundation 2010 databases to another server.

  1. Prepare to move the SharePoint Foundation 2010 databases to another server

  2. Stop SharePoint Foundation 2010 and Internet Information Services on the Source Server

  3. Detach the databases from the current SQL Server instance of SHAREPOINT on the Source Server

  4. Copy or move your SharePoint Foundation 2010 data to removable media or to the destination server

  5. Transfer the copied data to the Destination Server

  6. Confirm that SQL Server settings on the Destination Server are accurately mapped to the settings of the Source Server

  7. Add the Network Service account to the System Administrators role in SQL Server

  8. Attach the SharePoint Foundation 2010 databases to the new SQL Server instance

  9. Configure SQL Server settings on the Primary Server

  10. Restart SharePoint Foundation 2010 and Internet Information Services on the Destination Server

  11. Verify that websites built on SharePoint Foundation 2010 function correctly

  12. Disable server backup for SharePoint Foundation 2010 applications on the Source Server

  13. Set up SharePoint Foundation 2010 Backup (optional)

Prepare to move the SharePoint Foundation 2010 databases to another server

Before you move your SharePoint Foundation 2010 databases to another server, perform the following tasks:

  1. Install Windows SBS 2011 Premium Add-on on the Destination Server. If you are moving the databases to a second server, make sure that server is a member of the Windows SBS 2011 Standard network.

  2. Access the administration utility for your firewall, and make sure that port 1433 is open for TCP inbound connectivity.

  3. On the Source Server, close all Windows PowerShell management windows, and close any Command Prompt windows that are open.

Stop SharePoint Foundation 2010 and Internet Information Services on the Source Server

On the Source Server, stop all services that are related to SharePoint Foundation 2010 and Internet Information Services (IIS).

  1. Log on to the Source Server as an administrator.

  2. Click Start, point to Administrative Tools, and then click Services.

  3. Locate and click each of the following services, and then click Stop the service.

    • 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

  4. To open a Command Prompt window as an Administrator.

  5. At the command prompt, type iisreset /stop.

Detach the databases from the current SQL Server instance of SHAREPOINT on the Source Server

On the server running Windows SBS 2011 Standard, in SQL Server Management Studio, detach the databases from the current instance of server name\SHAREPOINT.

To detach the databases from the current SQL Server instance of SHAREPOINT

  1. Click Start, click All Programs, click Microsoft SQL Server 2008 R2, right-click SQL Server Management Studio, and then click Run as administrator.

  2. In the Connect to Server window, in the Server name box, type server name**\SHAREPOINT** (where server name is the name of your server), and then click Connect.

  3. In the console tree, expand Databases.

  4. For each of the following databases:

    • SharePoint_AdminContent

    • SharePoint_ConfigurationDatabase

    • ShareWebDB

    • WSS_Search

    Perform the following steps:

    1. Right-click the database, point to Tasks, and then click Detach.

    2. In the Detach Database console, select the Drop Connections check box, and then click OK.

Copy or move your SharePoint Foundation 2010 data to removable media or to the destination server

On the Source Server, open Windows Explorer and navigate to the folder that contains your SharePoint Foundation 2010 data. Locate the .mdf, .ldf, and .ndf files that are associated with each database that you detached in the previous step, and then copy or move the files to the destination server, or to removable media such as a CD, DVD, or USB flash drive.

Note

The default location of these files on the Source Server is C:\Program Files\Microsoft SQL Server\MSSQL10_50.SHAREPOINT\MSSQL\DATA.

Transfer the copied data to the Destination Server

On the Destination Server, connect the removable media that contains the file copies, and then use Windows Explorer to copy the files to the folder that contains your SharePoint Foundation 2010 data.

Note

The default location of these files on the Destination Server is %programfiles%\Microsoft SQL Server\MSSQL10_50.SHAREPOINT\MSSQL\DATA.

Attach the SharePoint Foundation 2010 databases to the new SQL Server instance

To attach the SharePoint Foundation 2010 databases to the new SQL Server instance

  1. From the Destination Server, click Start, click All Programs, click Microsoft SQL Server 2008 R2, click SQL Server Management Studio, and then click Run as administrator.

  2. In the Connect to Server window, in the Server name box, type <server name>\<instance name> (where <server name> is the name of your server), and then click Connect.

  3. In the console tree, expand Databases.

  4. For each of the following databases:

    • SharePoint_AdminContent

    • SharePoint_ConfigurationDatabase

    • ShareWebDB

    • WSS_Search

    Perform the following steps:

    1. Right-click the database, point to Tasks, and then click Attach.

Note

If there is an existing database by that name, make sure that you have a backup of the existing database, and then delete the database.

2.  Click **Add** and browse to the copy of your database files.  
      
3.  Click **OK**.  
      

Confirm that SQL Server settings on the Destination Server are accurately mapped to the settings of the Source Server

Confirm that the SQL Server configuration of the Destination Server is mapped accurately compared to the configuration of the Source Server. This includes confirming that the data logins, fixed server roles, fixed database roles, and permissions for the databases from the Source Server are configured correctly on the Destination Server.

To confirm that SQL Server settings on the Destination Server are accurately mapped

  1. On the Source Server, click Start, click All Programs, click Microsoft SQL Server 2008 R2, click SQL Server Management Studio Express, and then click Run as administrator.

  2. For Server name, type <second server name>\<instance name you installed>.

  3. Perform the following steps by using the values that are defined in the following table to create the required account logins.

    1. Expand the server name, right-click Security, point to New, and then click Login.

    2. On the General page, for Login Name, type the name of a required login account.

Note

You must change the account names to spfarm(1), spsearch(1), spwebapp(1). To view the names of the accounts that are configured for SharePoint 2010 Administration service, run the Services snap-in on the Destination Server.

3.  On the **Server Roles** page, select the server roles for the account as defined in the table.  
      
4.  On the **User Mapping** page, select the database mappings as defined in the table.  
      

Note

By default, it is not necessary to change the user mapping.

5.  Click **OK**.  
      

**Table of SQL Server Settings**
Account Name Server Role User Mapping: Database User Mapping: Database Role Membership

<domain name>\Spfarm(*)

public

Dbcreator

Securityadmin

SharePoint_AdminContent_<GUID>

public

Dbowner

SharePoint_Shell_Access

WSS_content_Application_Pools

SharePoint_ConfigurationDatabase

public

Dbowner

SharePoint_Shell_Access

WSS_content_Application_Pools

ShareWebDb

public

Dbowner

WSS_Search_<Server Name>

public

Dbowner

<domain name>\spSearch(*)

public

SharePoint_AdminContent_<GUID>

public

WSS_content_Application_Pools

SharePoint_ConfigurationDatabase

public

WSS_content_Application_Pools

WSS_Search_<Server Name>

public

<domain name>\spwebapp(*)

public

SharePoint_AdminContent_<GUID>

public

Dbowner

SharePoint_ConfigurationDatabase

public

WSS_content_Application_Pools

ShareWebDb

public

Dbowner

WSS_Search_<Server Name>

public

Dbowner

Add the Network Service account to the System Administrators role in SQL Server

Perform this step if you are moving data and settings to the Second Server on a Windows SBS 2011 Standard network.

To add the Network Service account to the System Administrators role in SQL Server

  1. From the Source Server, click Start, click All Programs, click Microsoft SQL Server 2008 R2, click SQL Server Management Studio Express, and then click Run as administrator.

  2. In the Connect to Server window, in the Server name box, type <Second Server name> (where <Second Server name> is the name of the server running Windows SBS 2011 Premium Add-on, and then click Connect.

  3. Expand the server name, right-click Security, point to New, and then click Login.

  4. For Login Name, type <domain name>\<Second Server name>$.

  5. Under Select a page, click Server Roles.

  6. In the Server roles list, select sysadmin, and then click OK.

Configure SQL Server settings on the Primary Server

Perform this step if you are moving data and settings to the Second Server on a Windows SBS 2011 Standard network.

To configure SQL Server settings on the Primary Server

  1. From the Primary Server, click Start, click All Programs, click Microsoft SQL Server 2008 R2, click Configuration Tools, and then click SQL Server Configuration Manager.

Note

If SQL Server Configuration Manager is not installed, you must run SQL Server setup to install it.

  1. Expand SQL Native Client 10.0 Configuration, right-click Aliases, and then click New Alias.

  2. In the Alias – New dialog box, specify the following settings:

    1. In the Alias Name text box, type the name of the original SQL Server instance.

    2. In the Port No text box, type 1433, and then click OK.

    3. In the Protocol text box, verify that TCP/IP is specified.

    4. In the Server text box, type the name of the Second Server that is hosting the SharePoint Foundation 2010 databases.

  3. Click OK to save the settings.

Restart SharePoint Foundation 2010 and Internet Information Services on the Destination Server

On the Destination Server, restart all the services that are related to SharePoint Foundation 2010 and Internet Information Services (IIS).

  1. Log on to the Destination Server as an administrator.

  2. Click Start, point to Administrative Tools, and then click Services.

  3. Locate and click each of the following services, and then click Restart the service.

    • 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

  4. To open a Command Prompt window as an Administrator.

  5. At the command prompt, type iisreset /start.

Verify that websites built on SharePoint Foundation 2010 function correctly

Perform this step from any computer on the Windows SBS 2011 Standard network.

To verify that websites built on SharePoint Foundation 2010 function correctly

  1. Open the Internet browser, and navigate to https://companyweb.

  2. Test the features of the website to ensure that the database move completed successfully.

Disable server backup for SharePoint Foundation 2010 applications on the Source Server

Perform this procedure from the Source Server.

To disable server backup for SharePoint Foundation 2010 applications on the Source Server

  1. To open a Command Prompt window as an Administrator.

  2. At the command prompt, type cd \%Program files%\Common Files\Microsoft Shared\Web Server Extensions\14\BIN, where %Program files% refers to the name of the folder where you installed the SharePoint Foundation 2010 programs (the default location is \Program Files), and then press ENTER.

  3. At the command prompt, type stsadm.exe –o unregisterwsswriter, and then press ENTER.

Set up SharePoint Foundation 2010 Backup (optional)

Note

Because a server backup cannot back up files and folders that are located another server, we recommend that you use the Backup feature included with SharePoint Foundation 2010 to back up your SharePoint Foundation 2010 data.

To set up SharePoint Foundation 2010 Backup

  1. On the Primary Server, click Start, click All Programs, click Microsoft SharePoint 2010 Products, and then click SharePoint 2010 Central Administration.

  2. In SharePoint 2010 Central Administration, click Backup and Restore.

  3. Click Configure backup settings, and then specify the information that is requested on the Default Backup and Restore Settings page.

  4. Click OK to save your settings.