Move content databases (SharePoint Foundation 2010)

Applies to: 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 Foundation 2010 Web application to another. You can move a content database to load-balance a database server or Web application.

Overview of moving content databases

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

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

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

3. In SharePoint Foundation, 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).

4. In SQL Server, detach the database from the current instance. For details, see To detach the content databases from SQL Server.

5. 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 (SharePoint Foundation 2010).

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

7. In SharePoint Foundation, 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 Foundation 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).

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

To record which content databases are associated with each Web application

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

2. On the Start menu, click All Programs.

3. Click Microsoft SharePoint 2010 Products.

4. Click SharePoint 2010 Management Shell.

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

6. Repeat for each Web application.

For more information, see Get-SPContentDatabase.

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

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

2. On the Start menu, click All Programs.

3. Click Microsoft SharePoint 2010 Products.

4. Click SharePoint 2010 Management Shell.

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

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

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

2. In Central Administration, in the Monitoring section, click Check Job Status.

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

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

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

2. In Central Administration, in the Application Management section, click Manage Content databases.

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

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

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

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

2. On the Start menu, click All Programs.

3. Click Microsoft SharePoint 2010 Products.

4. Click SharePoint 2010 Management Shell.

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

To detach the content databases from SQL Server

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

2. In SQL Server Management Studio, open the source SQL Server instance, and then expand the Databases node.

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

1. Verify that the user account that is performing this procedure has Write access to both the source and destination folders.

2. Using Windows Explorer, locate the .mdf, .ldf, and .ndf files for the content databases.

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

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

2. In Management Studio, open the destination SQL Server instance.

3. Right-click the Databases node, point to Tasks, and then click Attach.

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

5. Repeat for each content database that you are moving.

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

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

2. In Central Administration, in the Application Management section, click Manage Content databases.

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

4. On the Add Content Database page, verify that the Web Application menu displays the correct Web application.

5. In the Server box, specify the database server that hosts the database.

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

7. Specify the authentication method for the database, and then click OK.

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

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

2. On the Start menu, click All Programs.

3. Click Microsoft SharePoint 2010 Products.

4. Click SharePoint 2010 Management Shell.

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

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

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

2. On the Start menu, click All Programs.

3. Click Microsoft SharePoint 2010 Products.

4. Click SharePoint 2010 Management Shell.

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

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

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

2. In Central Administration, in the Monitoring section, click Check Job Status.

3. For each scheduled job that you disabled previously, click the job to open the Edit Timer Job page, click Enable, and then click OK.