Move content databases (SharePoint Server 2010)

Article
07/22/2014

Applies to: SharePoint Server 2010, SharePoint Foundation 2010

This article describes how you can move content databases between servers that are running Microsoft SQL Server, between instances of SQL Server, or from one Microsoft SharePoint Server 2010 Web application to another. You can move a content database to load-balance a database server or Web application.

Important

This article only describes how to move content databases. For information about how to move other kinds of databases that are associated with SharePoint Server 2010, see Rename or move service application databases (SharePoint Server 2010) and Move all databases (SharePoint Server 2010).

Overview of moving content databases

When you move content databases, you must use both SharePoint Server 2010 tools and SQL Server tools. You can use either the SharePoint Server Central Administration Web site or Windows PowerShell 2.0. The following list summarizes how to move content databases:

Record the name of the content database and which Web application it is associated with. For details, see To record which content databases are associated with each Web application.
Pause any service applications and services that may attempt to run against the database, including timer jobs and search crawls. For details, see To pause timer jobs by using Windows PowerShell (option 1) or To pause timer jobs by using Central Administration (option 2).
In SharePoint Server, remove the content database from the Web application. You can perform this action by using Central Administration or Windows PowerShell 2.0. In general, if you are only working with more than one database, it may be faster to write a Windows PowerShell 2.0 script. For details, see To detach the content databases from a Web application by using Central Administration (option 1) or To detach content databases from a Web application by using Windows PowerShell (option 2).
In SQL Server, detach the database from the current instance. For details, see To detach the content databases from SQL Server.
Using Windows Explorer, copy or move the .mdf, .ndf, and .ldf files associated with the database from the source location to the destination location. For details, see To move the content databases to a new location.

Note

You can also back up and restore databases to move them. However, backup and recovery are not described in this article. For information, see Backup and recovery for SharePoint Server 2010.
In SQL Server, attach the database to the new instance. For details, see To attach the content databases to the new instance of SQL Server.
In SharePoint Server, add the content database to the destination Web application. Be sure that you use exactly the same name when you reattach the content database. Otherwise, SharePoint Server creates a new content database. You can use either Central Administration or Windows PowerShell 2.0 to perform this action. For details, see To attach the content databases to the Web application by using Central Administration (option 1) or To attach content databases to a Web application by using Windows PowerShell (option 2).
Restart any service applications and services that should be run against the database, including timer jobs and search crawls. For details, see To restart timer jobs by using Windows PowerShell (option 1) or To restart timer jobs by using Central Administration (option 2).

Moving content databases

Note

Membership in the Administrators group on the local computers is required to complete the following procedures. If you are running SharePoint Server 2010 in a least-privileged environment, and you are running procedures from within Central Administration, ensure that you have the following roles in SQL Server:

The dbowner fixed database role for the configuration database and the content database on the source server, in order to detach the content database.
The dbcreator and securityadmin fixed server roles on the destination server, in order to attach the database and configure SQL Server logins.

Note

If you are moving a content database to a different farm, you must make the server farm account a member of the Administrators group on the database server during the restore process. This enables the account to replicate the security setting for the databases. This access level can be removed after the content database has been moved. For more information, see Account permissions and security settings (SharePoint Server 2010).
The destination farm must be running the same version or a later version of SharePoint Server 2010 than the source farm is running.

To record which content databases are associated with each Web application

Verify that you meet the following minimum requirements: See Add-SPShellAdmin.
On the Start menu, click All Programs.
Click Microsoft SharePoint 2010 Products.
Click SharePoint 2010 Management Shell.
At the Windows PowerShell command prompt, type the following command:
```
Get-SPContentDatabase -WebApplication <http://SiteName>
```
Where <http://SiteName> is the URL of the Web application.
Repeat for each Web application.

For more information, see Get-SPContentDatabase.

Note

We recommend that you use Windows PowerShell when performing command-line administrative tasks. The Stsadm command-line tool has been deprecated, but is included to support compatibility with previous product versions.

To pause timer jobs by using Windows PowerShell (option 1)

Verify that you meet the following minimum requirements: See Add-SPShellAdmin.
On the Start menu, click All Programs.
Click Microsoft SharePoint 2010 Products.
Click SharePoint 2010 Management Shell.
At the Windows PowerShell command prompt, type the following command:
```
Get-SPTimerJob -webapplication <http://WebApplicationURL> | select name | Out-File <c:\timerjobfile.txt> -Append -Encoding ascii

ForEach($tmrjob in (Get-Content <c:\timerjobfile.txt>)) { Get-SPTimerJob -Identity $tmrjob | Disable-SPTimerjob }
```
Where:
- <http://WebApplicationURL> is the Web application associated with the content database that you are moving.
- <c:\timerjobfile.txt> is the location of the file that you are creating that lists all timer jobs associated with the Web application.

For more information, see Get-SPTimerJob, Out-File, ForEach-Object, Get-Content, and Disable-SPTimerJob.

Note

To pause timer jobs by using Central Administration (option 2)

Verify that the user account that is performing this procedure is a member of the Farm Administrators SharePoint group.
In Central Administration, in the Monitoring section, click Check Job Status.
For each scheduled job that runs against the content database that you are moving, click the job to open the Edit Timer Job page, click Disable, and then click OK.
If you are running the Search service application and crawling the content database, follow the steps to pause the crawl in the article Start, pause, resume, or stop a crawl (SharePoint Server 2010).

To detach the content databases from a Web application by using Central Administration (option 1)

Verify that the user account that is performing this procedure is a member of the Farm Administrators SharePoint group.
In Central Administration, in the Application Management section, click Manage Content databases.
On the Manage Content Databases page, click the content database that you want to move.

The Manage Content Database Settings page opens.

Note

If the content database does not appear in the list, it might be associated with another Web application. To select another Web application, on the Web Application menu, click Change Web Application.
On the Manage Content Database Settings page, in the Remove Content Database section, select the Remove content database check box, and then click OK.

Note

Removing the content database does not delete the database; it only removes the association of the database with the Web application.
Repeat steps 3 and 4 for each content database that you want to move.

To detach content databases from a Web application by using Windows PowerShell (option 2)

Verify that you meet the following minimum requirements: See Add-SPShellAdmin.
On the Start menu, click All Programs.
Click Microsoft SharePoint 2010 Products.
Click SharePoint 2010 Management Shell.
At the Windows PowerShell command prompt, type the following command:
```
Dismount-SPContentDatabase "<ContentDB>" 
```
Where <ContentDB> is the name of the content database.

Note

If you have multiple content databases that have the same name, you must use the content database GUID in this command instead of using the content database name. To retrieve the GUID of the content database, run the Get-SPContentDatabase cmdlet without arguments.

For more information, see Dismount-SPContentDatabase and Get-SPContentDatabase.

Note

To detach the content databases from SQL Server

Verify that the user account that is performing this procedure is a member of the db_owner fixed database role on the database server where each database is stored.
In SQL Server Management Studio, open the source SQL Server instance, and then expand the Databases node.
Right-click the content database, point to Tasks, and then click Detach. Repeat this step for each content database that you want to move.

Note

Use this procedure to move only content databases. Do not detach any other kinds of databases.

To move the content databases to a new location

Verify that the user account that is performing this procedure has Write access to both the source and destination folders.
Using Windows Explorer, locate the .mdf, .ldf, and .ndf files for the content databases.
Select the .mdf, .ldf, and .ndf files for the database that you want to move and either copy or move them to the destination directory.

To attach the content databases to the new instance of SQL Server

Verify that the user account that is performing this procedure is a member of the dbcreator fixed server role on the database server where each database is stored.
In Management Studio, open the destination SQL Server instance.
Right-click the Databases node, point to Tasks, and then click Attach.
In the Attach Database dialog box, browse to where you transferred the .mdf, .ldf, and .ndf files, select the .mdf file for the database that you want to attach, and then click OK.
Repeat for each content database that you are moving.

To attach the content databases to the Web application by using Central Administration (option 1)

Verify that the user account that is performing this procedure is a member of the Farm Administrators group.
In Central Administration, in the Application Management section, click Manage Content databases.
On the Manage Content Databases page, click Add a content database.
On the Add Content Database page, verify that the Web Application menu displays the correct Web application.
In the Server box, specify the database server that hosts the database.
In the Database Name box, type the exact name of the transferred content database.

Note

Verify that the name is correct. If it is not, a new database will be created.
Specify the authentication method for the database, and then click OK.
Repeat these steps for each database that you are adding. Be sure that you select the correct Web application from the Web Application menu for each database.

To attach content databases to a Web application by using Windows PowerShell (option 2)

Verify that you meet the following minimum requirements: See Add-SPShellAdmin.
On the Start menu, click All Programs.
Click Microsoft SharePoint 2010 Products.
Click SharePoint 2010 Management Shell.
At the Windows PowerShell command prompt, type the following command:
```
Mount-SPContentDatabase "<ContentDB>" -DatabaseServer "<DBServer>" -WebApplication <http://SiteName>
```
Where:
- <ContentDB> is the content database to be attached.
- <DBServer> is the name of the database server.
- <http://SiteName> is the URL of the Web application to which the content database is being attached.

For more information, see Mount-SPContentDatabase.

Note

To restart timer jobs by using Windows PowerShell (option 1)

Verify that you meet the following minimum requirements: See Add-SPShellAdmin.
On the Start menu, click All Programs.
Click Microsoft SharePoint 2010 Products.
Click SharePoint 2010 Management Shell.
At the Windows PowerShell command prompt, type the following command:
```
ForEach($tmrjob in (Get-Content <c:\timerjobfile.txt>)) {Get-SPTimerJob -Identity $tmrjob | Enable-SPTimerjob}
```
Where:
- <c:\timerjobfile.txt> is the location of the file that you created that lists all of the timer jobs associated with the Web application.

For more information, see Get-SPTimerJob, ForEach-Object, Get-Content, and Enable-SPTimerJob.

Note

To restart timer jobs by using Central Administration (option 2)

Verify that the user account that is performing this procedure is a member of the Farm Administrators group.
In Central Administration, in the Monitoring section, click Check Job Status.
For each scheduled job that you disabled previously, click the job to open the Edit Timer Job page, click Enable, and then click OK.
If you are running the Search service application and crawling the content database, follow the steps to resume the crawl in the article Start, pause, resume, or stop a crawl (SharePoint Server 2010). You must run a full crawl of the content database.

Share via

Move content databases (SharePoint Server 2010)

Overview of moving content databases

Moving content databases

See Also

Other Resources

Additional resources