Export (0) Print
Expand All
4 out of 5 rated this helpful - Rate this topic

Move content databases in SharePoint 2013

Published: July 16, 2012

Summary: Learn how to move content databases in SharePoint 2013.

Applies to:  SharePoint Foundation 2013 | SharePoint Server 2013 

This article describes how to move content databases between servers that are running SQL Server, between instances of SQL Server, or from one SharePoint 2013 web application to another.

Important 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 2013, see Rename service application databases in SharePoint 2013 and Move all databases in SharePoint 2013.

You can move content databases by using the SharePoint Central Administration website or Windows PowerShell, and SQL Server tools. Which tool that you use depends on what kind of environment you have deployed, what your schedule requires, and what service level agreements that you have made with your organization.

In this article:

Before you begin

Before you begin this operation, moving a content database, review the following tasks. Each task is a procedure that must be done in the order in which it is listed. Note that when you move content databases, you must use both SharePoint 2013 tools and SQL Server tools. You can use either Central Administration or Windows PowerShell 3.0 for this operation.

  1. Record the content database name and the web application it is associated with.

  2. Pause any service applications and services that might run against the content database, including timer jobs and search crawls.

  3. Remove the SharePoint 2013 content database from the web application.

  4. Detach the content database from the current SQL Server instance.

  5. Copy or move the content database .mdf, .ndf, and .ldf files from the source location to the destination location using Windows Explorer.

  6. Attach the content database to the new SQL Server instance.

  7. Add the content database to the destination web application in SharePoint 2013.

    Important Important:

    Use the identical name when you add the content database or SharePoint 2013 creates a new content database.

  8. Restart all service applications and services that you paused in step 2.

note Note:

Because SharePoint 2013 runs as websites in Internet Information Services (IIS), administrators and users depend on the accessibility features that browsers provide. SharePoint 2013 supports the accessibility features of supported browsers. For more information, see the following resources:

Moving content databases by using Central Administration

Use the following procedure to move the content databases in your SharePoint 2013 farm by using Central Administration.

note Note:

The procedures in this section use Central Administration to move content databases. However when you perform the following procedures, you must use the correct tool:

  • 1. To record which content databases are associated with each web application ─ Windows PowerShell

  • 4. To detach the content databases from SQL Server ─ SQL Server tools

  • 5. To move the content databases to a new location ─ Windows Explorer

  • 6. To attach the content databases to the new instance of SQL Server ─ SQL Server tools

note Note:

The procedures in this section use Central Administration to move content databases. However, the first procedure, must be performed by using Windows PowerShell.

note 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 is moved. For more information, see Account permissions and security settings in SharePoint 2013.

The destination farm must be running the same version or a later version of SharePoint 2013 than the source farm is running.

1. To record which content databases are associated with each web application

  1. Verify that you have the following memberships:

    • securityadmin fixed server role on the SQL Server instance.

    • db_owner fixed database role on all databases that are to be updated.

    • Administrators group on the server on which you are running the Windows PowerShell cmdlets.

    • The dbcreator and securityadmin fixed server roles on the destination server, in order to attach the database and configure SQL Server logins.

    An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 2013 cmdlets.

    note Note:

    If you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about Windows PowerShell permissions, see Add-SPShellAdmin.

  2. Start the SharePoint 2013 Management Shell.

    • For Windows Server 2008 R2:

      • On the Start menu, click All Programs, click Microsoft SharePoint 2013 Products, and then click SharePoint 2013 Management Shell.

    • For Windows Server 2012:

      1. On the Start screen, click SharePoint 2013 Management Shell.

        If SharePoint 2013 Management Shell is not on the Start screen:

      2. Right-click Computer, click All apps, and then click SharePoint 2013 Management Shell.

    For more information about how to interact with Windows Server 2012, see Common Management Tasks and Navigation in Windows Server 2012.

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

    noteNote:

    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.

2. To pause timer jobs 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. 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.

3. To detach the content databases from a web application 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. 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 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 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.

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

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

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

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

7. To attach the content databases to the web application by using Central Administration

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

8. To restart timer jobs by using Central Administration

  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.

Moving content databases by using Windows PowerShell

Use the following procedure to move the content databases in your SharePoint 2013 farm by using Windows PowerShell.

note Note:

The procedures in this section use Windows PowerShell to move content databases. However when you perform the following procedures, you must use the correct tool:

  • 4. To detach the content databases from SQL Server ─ SQL Server tools

  • 5. To move the content databases to a new location ─ Windows Explorer

  • 6. To attach the content databases to the new instance of SQL Server ─ SQL Server tools

note 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 is moved.

The destination farm must be running the same version or a later version of SharePoint 2013 than the source farm is running.

1. To record which content databases are associated with each web application

  1. Verify that you have the following memberships:

    • securityadmin fixed server role on the SQL Server instance.

    • db_owner fixed database role on all databases that are to be updated.

    • Administrators group on the server on which you are running the Windows PowerShell cmdlets.

    • The dbcreator and securityadmin fixed server roles on the destination server, in order to attach the database and configure SQL Server logins.

    An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 2013 cmdlets.

    note Note:

    If you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about Windows PowerShell permissions, see Add-SPShellAdmin.

  2. On the Start menu, click All Programs.

  3. Click Microsoft SharePoint 2013 Products.

  4. Click SharePoint 2013 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.

For more information, see Get-SPContentDatabase

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

2. To pause timer jobs by using Windows PowerShell

  1. Verify that you have the following memberships:

    • securityadmin fixed server role on the SQL Server instance.

    • db_owner fixed database role on all databases that are to be updated.

    • Administrators group on the server on which you are running the Windows PowerShell cmdlets.

    • The dbcreator and securityadmin fixed server roles on the destination server, in order to attach the database and configure SQL Server logins.

    An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 2013 cmdlets.

    note Note:

    If you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about Windows PowerShell permissions, see Add-SPShellAdmin.

  2. On the Start menu, click All Programs.

  3. Click Microsoft SharePoint 2013 Products.

  4. Click SharePoint 2013 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.

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

3. To detach content databases from a web application by using Windows PowerShell

  1. Verify that you have the following memberships:

    • securityadmin fixed server role on the SQL Server instance.

    • db_owner fixed database role on all databases that are to be updated.

    • Administrators group on the server on which you are running the Windows PowerShell cmdlets.

    • The dbcreator and securityadmin fixed server roles on the destination server, in order to attach the database and configure SQL Server logins.

    An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 2013 cmdlets.

    note Note:

    If you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about Windows PowerShell permissions, see Add-SPShellAdmin.

  2. On the Start menu, click All Programs.

  3. Click Microsoft SharePoint 2013 Products.

  4. Click SharePoint 2013 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.

    noteNote:

    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.

    noteNote:

    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.

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

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

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

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

7. To attach content databases from a web application by using Windows PowerShell

  1. Verify that you have the following memberships:

    • securityadmin fixed server role on the SQL Server instance.

    • db_owner fixed database role on all databases that are to be updated.

    • Administrators group on the server on which you are running the Windows PowerShell cmdlets.

    • The dbcreator and securityadmin fixed server roles on the destination server, in order to attach the database and configure SQL Server logins.

    An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 2013 cmdlets.

    note Note:

    If you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about Windows PowerShell permissions, see Add-SPShellAdmin.

  2. On the Start menu, click All Programs.

  3. Click Microsoft SharePoint 2013 Products.

  4. Click SharePoint 2013 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 name of 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 Dismount-SPContentDatabase.

    noteNote:

    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.

8. To restart timer jobs by using Windows PowerShell

  1. Verify that you have the following memberships:

    • securityadmin fixed server role on the SQL Server instance.

    • db_owner fixed database role on all databases that are to be updated.

    • Administrators group on the server on which you are running the Windows PowerShell cmdlets.

    • The dbcreator and securityadmin fixed server roles on the destination server, in order to attach the database and configure SQL Server logins.

    An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 2013 cmdlets.

    note Note:

    If you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about Windows PowerShell permissions, see Add-SPShellAdmin.

  2. On the Start menu, click All Programs.

  3. Click Microsoft SharePoint 2013 Products.

  4. Click SharePoint 2013 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.

    noteNote:

    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.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.