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
Click Start.
In the search box, type cmd.
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.
Prepare to move the SharePoint Foundation 2010 databases to another server
Stop SharePoint Foundation 2010 and Internet Information Services on the Source Server
Detach the databases from the current SQL Server instance of SHAREPOINT on the Source Server
Copy or move your SharePoint Foundation 2010 data to removable media or to the destination server
Transfer the copied data to the Destination Server
Confirm that SQL Server settings on the Destination Server are accurately mapped to the settings of the Source Server
Add the Network Service account to the System Administrators role in SQL Server
Attach the SharePoint Foundation 2010 databases to the new SQL Server instance
Configure SQL Server settings on the Primary Server
Restart SharePoint Foundation 2010 and Internet Information Services on the Destination Server
Verify that websites built on SharePoint Foundation 2010 function correctly
Disable server backup for SharePoint Foundation 2010 applications on the Source Server
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:
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.
Access the administration utility for your firewall, and make sure that port 1433 is open for TCP inbound connectivity.
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).
To stop services related to SharePoint Foundation 2010 and IIS
Log on to the Source Server as an administrator.
Click Start, point to Administrative Tools, and then click Services.
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
To open a Command Prompt window as an Administrator.
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
Click Start, click All Programs, click Microsoft SQL Server 2008 R2, right-click SQL Server Management Studio, and then click Run as administrator.
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.
In the console tree, expand Databases.
For each of the following databases:
SharePoint_AdminContent
SharePoint_ConfigurationDatabase
ShareWebDB
WSS_Search
Perform the following steps:
Right-click the database, point to Tasks, and then click Detach.
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
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.
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.
In the console tree, expand Databases.
For each of the following databases:
SharePoint_AdminContent
SharePoint_ConfigurationDatabase
ShareWebDB
WSS_Search
Perform the following steps:
- 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
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.
For Server name, type <second server name>\<instance name you installed>.
Perform the following steps by using the values that are defined in the following table to create the required account logins.
Expand the server name, right-click Security, point to New, and then click Login.
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
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.
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.
Expand the server name, right-click Security, point to New, and then click Login.
For Login Name, type <domain name>\<Second Server name>$.
Under Select a page, click Server Roles.
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
- 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.
Expand SQL Native Client 10.0 Configuration, right-click Aliases, and then click New Alias.
In the Alias – New dialog box, specify the following settings:
In the Alias Name text box, type the name of the original SQL Server instance.
In the Port No text box, type 1433, and then click OK.
In the Protocol text box, verify that TCP/IP is specified.
In the Server text box, type the name of the Second Server that is hosting the SharePoint Foundation 2010 databases.
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).
To restart other services related to SharePoint Foundation 2010 and IIS
Log on to the Destination Server as an administrator.
Click Start, point to Administrative Tools, and then click Services.
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
To open a Command Prompt window as an Administrator.
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
Open the Internet browser, and navigate to https://companyweb.
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
To open a Command Prompt window as an Administrator.
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.
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
On the Primary Server, click Start, click All Programs, click Microsoft SharePoint 2010 Products, and then click SharePoint 2010 Central Administration.
In SharePoint 2010 Central Administration, click Backup and Restore.
Click Configure backup settings, and then specify the information that is requested on the Default Backup and Restore Settings page.
Click OK to save your settings.