Move all databases in SharePoint Server

 

Applies to: SharePoint Foundation 2013, SharePoint Server 2013, SharePoint Server 2016

Topic Last Modified: 2017-09-14

Summary: Learn how to move all databases associated with SharePoint Server 2016 and SharePoint 2013 to a new database server.

You can use the SharePoint Central Administration website, or SQL Server tools to move all databases that are associated with SharePoint Server to a new database server.

The procedures in this article explain how to move the following kinds of databases that are hosted on a single database server:

  • Configuration database

  • Central Administration content database

  • Content databases

  • Service application databases

ImportantImportant:
To move database files within the same instance of SQL Server we recommend that you use the FILENAME clause of the ALTER DATABASE statement. For more information, see Move User Databases.
To move a database to another instance of SQL Server or to another server, we recommend that you use procedures found in Database Detach and Attach (SQL Server) or Back Up and Restore of SQL Server Databases.

The following are the minimum permissions that are required to perform this process:

  • You must be a member of the Farm Administrators SharePoint group.

  • On the computer that is running the SharePoint Central Administration Web site, you must be a member of the Administrators group.

  • On the database server from which the databases are being moved, you must be a member of the following:

    • The Administrators group

    • The db_backupoperator fixed database role

  • On the database server to which the databases are being moved, you must be a member of the following:

    • The Administrators group

    • The db_owner fixed database role

In some environments, you must coordinate the move procedures with the database administrator. Be sure to follow applicable policies and guidelines for managing databases.

ImportantImportant:
When you move databases, all farm sites and assets are unavailable to users until the process is complete. Try to complete this operation outside normal business hours.

To move all databases from one database server to another database server, you have to work in both SharePoint Server and SQL Server.

Before you begin this operation, review the steps in this process:

  1. Prepare the new database server.

  2. Close all open SharePoint Management Shell windows.

  3. Stop all services that are related to SharePoint Server and Internet Information Services (IIS).

  4. Detach the databases from the current SQL Server instance.

  5. Copy or move all files that are associated with the databases (.mdf, .ndf, and .ldf), to the new destination server that runs SQL Server.

  6. Make sure that all of the SQL Server logins, fixed server roles, fixed database roles, and permissions for the databases are configured correctly on the new destination database server.

    NoteNote:
    It is important that the destination server where you move the databases has the same database information that the current SQL Server instance has. For details about how to do this, see How to transfer logins and passwords between instances of SQL Server. For more information, see Server-Level Roles and Database-Level Roles.
  7. Attach the databases to the new destination server that runs SQL Server.

  8. Use SQL Server connection aliases to point to the new database server and update all web servers.

    If you do not want to use SQL Server connection aliases use one of the following procedures to update the database connections for your SharePoint Server farm.

    • Scenario 1: Use this procedure to update the database connections if you use SharePoint Server and SQL Server AlwaysOn Availability Groups for high availability or disaster recovery.

    • Scenario 2: Use this procedure if you must use manual steps or if you move the databases from a SharePoint Server Single-server farm role installation to a new Single-server farm role installation.

  9. Restart all services that you stopped in step 3.

To prepare the new database server

  • Use the procedures in Configure SQL Server security for SharePoint Server to configure the new database server.

    NoteNote:
    The new database server must run either the same version of Windows Server and SQL Server as the existing database server or one of the following versions:
    For SharePoint Server 2016:
    • Windows Server 2012 R2

    • Windows Server 2016

    • SQL Server 2014 Service Pack 1 (SP1)

    • SQL Server 2016

    For SharePoint 2013:
    • Windows Server 2008 R2

    • Windows Server 2008 R2 Service Pack 1 (SP1)

    • Windows Server 2012

    • SQL Server 2008

    • SQL Server 2012

    • SQL Server 2014

    The version of the existing SharePoint Server and Windows Server must also support the version of the new SQL Server where the DBs are being moved. For more information, see Hardware and software requirements for SharePoint Server 2016 and Hardware and software requirements for SharePoint 2013.

To close all open sessions of SharePoint Management Shell

  1. Close all open SharePoint Management Shell windows, and all open command prompt windows.

To stop the farm

  1. On the server that is running Central Administration, stop the following services:

    • SharePoint Administration

    • SharePoint Timer

    • SharePoint Tracing

    • SharePoint User Code Host

    • SharePoint VSS Writer

    • World Wide Web Publishing Service

    • SharePoint Server Search 16

  2. On the server that is running Central Administration, at a command prompt, type iisreset /stop.

To detach databases

  1. In SQL Server Management Studio on the original database server, detach the databases that you want to move from the instance to which they are attached. If you are running many databases, you may want to run a Transact-SQL script to detach databases.

    NoteNote:
    A database cannot be detached if any one of the following is true:
    • The database is being mirrored.

    • A database snapshot exists on the database.

    For more information, see:

To move database files to the new server

  1. Verify that the user account that is performing this procedure is a member of the following:

    • On the database server from which the databases are being moved, you must be a member of the following:

      • The Administrators group

      • The db_backupoperator fixed database role

    • On the database server to which the databases are being moved, you must be a member of the following:

      • The Administrators group

      • The db_owner fixed database role

  2. Use Windows Explorer to locate the .mdf, .ldf, and .ndf files that are associated with each database that you are moving.

  3. Copy or move the files to the destination directory on the new computer that is running SQL Server.

To set up permissions on the new server

  1. Verify that the user account that is performing this procedure is a member of the following:

    • The Administrators group

    • The db_owner fixed database role

  2. On the destination database server, start Management Studio and transfer your logon credentials and permissions from the original instance to the destination instance. We recommend that you transfer permissions by running a script. An example script is available in How to transfer logins and passwords between instances of SQL Server.

    For more information about how to transfer SQL Server metadata between instances, see Managing Metadata When Making a Database Available on Another Server Instance.

To attach databases to the new instance of SQL Server

  1. Verify that the user account that is performing this procedure is a member of the following:

    • The Administrators group

    • The db_owner fixed database role

  2. On the destination database server, attach the databases to the new instance. For more information, see Attach a Database and sp_attach_db (Transact-SQL).

The following procedures provide methods to connect to the new SQL Server instance or update the database connections. Use the procedure that works best for your SharePoint Server farm environment.

To point the web application to the new database server by setting up SQL Server connection aliases

  1. This procedure must be performed on all servers in the SharePoint Server farm that connect to the instance of SQL Server that hosts the databases.

  2. Verify that the user account that is performing this procedure is a member of the following:

    • The Administrators group

    • The db_owner fixed database role

  3. Start the SQL Server Configuration Manager.

  4. Expand SQL Server Native Client Configuration, right-click Aliases, and then click New Alias.

  5. In the Alias Name box, enter the name of the new alias, which you are creating.

  6. In the Port Number box, enter the number of the port the new alias will use to connect to SQL Server.

  7. In the Protocol box, click the drop-down arrow and select TCP/IP. This is the protocol the new alias will use to connect to the SQL Server.

  8. In the Server box, enter the name of the current instance of SQL Server.

  9. Repeat steps 3 through 8 on all servers in the farm that connect to the new instance of SQL Server. For more information, see Create or Delete a Server Alias for Use by a Client (SQL Server Configuration Manager

  10. Optional. If your environment relies on System Center 2012 - Data Protection Manager (DPM) or a third-party application that uses the Volume Shadow Copy Service framework for backup and recovery, you must install the SQL Server connectivity components on each web server or application server by running SQL Server setup. For more information, see Install SQL Server 2014 from the Installation Wizard (Setup) and Windows Server Installation and Upgrade.

NoteNote:
You can use these Microsoft PowerShell cmdlets to deploy, manage, and remove availability groups in SQL Server with SharePoint Server :
  • Add-DatabaseToAvailabilityGroup

  • Remove-DatabaseFromAvailabilityGroup

  • Get-AvailabilityGroupStatus

Use the following procedure to update the database connections if you use SharePoint Server and SQL Server AlwaysOn Availability Groups for high availability or disaster recovery.

Scenario 1: To update the database connections by using 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 PowerShell cmdlets.

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

    NoteNote:
    If you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about PowerShell permissions, see Add-SPShellAdmin.
  2. Start the SharePoint Management Shell.

  3. At the PowerShell command prompt, type the following commands:

    Add-DatabaseToAvailabilityGroup -AGName "<AGGroupName>" -DatabaseName "<DatabaseName>" [-FileShare "<\\server\share>"]
    

    Where:

    • <AGGroupName> is the name of the Avaliability Group.

    • <DatabaseName> is the name of the database that you are adding to the Availability Group

    • If the optional -FileShare parameter is used, <\\server\share> is the name of the server and the share that you use.

  4. Repeat these steps for all databases that you move, including the Configuration and Central Administration Content databases.

Use the next procedure for the following scenarios:

Scenario 2: To update the database connections by using Microsoft PowerShell

  1. Start the SharePoint Management Shell.

  2. At the PowerShell command prompt, type the following commands:

    $db = get-spdatabase -identity <guid>
    

    Where:

    • <GUID> is the ID of the database that you move.

    NoteNote:
    Use Get-spdatabase without parameters to see a list of all databases with GUIDs.
    $db.ChangeDatabaseInstance("<DBServerName>")
    

    Where:

    • <DBServerName> is the name or alias of the new SQL Server or is the AlwaysOn Availability Group listener DNS name.

    • $db.Update()
      

  3. If you use SQL Server database mirroring then you must remember to populate the FailoverServiceInstance property on the SharePoint database.

    $db.failoverserviceinstance("<DBServerName>")
    

    Where:

    • <DBServerName> is the name or alias of the mirrored SQL Server.

      $db.update()
      
  4. Repeat these steps for all databases that you move, including the Configuration and Central Administration Content databases.

To restart the services in the farm

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

  2. On the server that is running the SharePoint Central Administration website, at a command prompt, type iisreset /start.

  3. In the Microsoft Management Console Services snap-in, start all of the services that are related to SharePoint Server and IIS. These include the following services:

    • SharePoint Administration

    • SharePoint Timer

    • SharePoint Tracing

    • SharePoint User Code Host

    • SharePoint VSS Writer

    • World Wide Web Publishing Service

    • SharePoint Server Search

Show: