Attach or detach content databases (SharePoint Server 2010)

 

Applies to: SharePoint Server 2010, SharePoint Foundation 2010

This article describes how to attach or detach Microsoft SharePoint Server 2010 content databases by using the SharePoint Central Administration Web site or by using Windows PowerShell 2.0.

You might want to attach or detach content databases in the following situations:

• You want to add a new content database for new site collections to keep content databases at a manageable size.

• You have restored a content database from another farm and you want the sites that it contains to be accessible from a Web application.

• You have archived site collections and you no longer need as many content databases. You can move the remaining site collections out of a content database and then detach the content database from the Web application. For more information, see Move site collections between databases (SharePoint Server 2010).

The steps to add a database and to attach a database are very similar. For more information about how to add a database, see Add a content database (SharePoint Server 2010).

To attach a content database by using Central Administration

1. Verify that the user account that is being used to perform this operation is a member of the Farm Administrators SharePoint group.

   If you want to create a new content database at the same time that you attach it, the SharePoint farm service account must be a member of the SQL Server dbcreator fixed server role. To attach a content database to a Web application, the SharePoint farm service account must have db_owner permission for the content database.

   Tip

   If the database already exists, it must be the same version as the Microsoft SharePoint 2010 Products farm or this operation will fail. To attach a content database that is a different version than the farm, use the Windows PowerShell 2.0 command in the following section.

2. On the SharePoint Central Administration Web site, click Application Management.

3. On the Application Management page, in the Databases section, click Manage content databases.

4. On the Manage Content Databases page, click Add a content database.

5. On the Add Content Database page:

   1. Use the Web Application drop-down menu to select the Web application to which you want to attach a content database.

   2. Specify the database server that hosts the database.

   3. Specify the database name. If the database does not already exist, it will be created.

   4. Specify the authentication method for the database, and supply an account name and password if you are using SQL authentication.

      Important

      The account name and password must already exist as a SQL Server login. We recommend that you use Windows authentication instead of SQL authentication because, by default, SQL authentication sends an unencrypted password to the computer that is running SQL Server. If you use SQL authentication, the SQL account requires the same SQL permissions as the SharePoint farm service account.

   5. Click OK.

To detach a content database by using Central Administration

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

2. On the SharePoint Central Administration Web site, click Application Management.

3. On the Application Management page, in the Databases section, click Manage content databases.

4. Select the Web application for which you want to detach a content database.

5. Click the content database that you want to detach.

6. On the Manage Content Database Settings page, select the Remove content database check box.

   If the content database contains data, you will receive a warning. Click OK to continue with the operation.

7. Click OK to confirm the detachment, or click Cancel to stop the operation without detaching the database.

   After detaching the content database in Central Administration, the content database still exists in SQL Server. If you want to permanently remove the content database, you must do so by using a SQL Server procedure.

To attach or detach a content database by using Windows PowerShell

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin. The user account must also be a member of the SQL Server dbcreator fixed server role on the SQL Server instance where the content database exists.

2. If you are using the Remove-SPContentDatabase cmdlet to delete the content database from SQL Server at the same time that you detach it, you must also be a member of the SharePoint_Shell_Access role on the content database.

3. On the Start menu, click All Programs.

4. Click Microsoft SharePoint 2010 Products.

5. Click SharePoint 2010 Management Shell.

6. At the Windows PowerShell command prompt, type the appropriate command.

   • To attach an existing content database:

     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 name of the Web application to which the content database is being attached.

   • To detach a content database:

     Dismount-SPContentDatabase ""

   Where <ContentdBName> is the name of the content database.

   Important

   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 with no arguments.

   The Dismount-SPContentDatabase cmdlet detaches the content database from the Web application, but it does not delete the content database from SQL Server. After a content database is detached, you cannot delete it by using Windows PowerShell 2.0. You can only remove it by using SQL Server tools. If you want to delete the content database from SQL Server while you detach it, use the Remove-SPContentDatabase cmdlet instead.

For more information, see Mount-SPContentDatabase and Dismount-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.

See Also

Reference

Get-SPContentDatabase
New-SPContentDatabase
Remove-SPContentDatabase