Move content databases (SharePoint Server 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.

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:

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

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.

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 Central Administration (option 2)

Verify that the user account that is performing this procedure is a member of the Farm Administrators SharePoint group.

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.

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 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.

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.

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 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.