Export (0) Print
Expand All

Move all databases in SharePoint 2013

 

Applies to: SharePoint Server 2013, SharePoint Foundation 2013

Topic Last Modified: 2014-11-07

Summary: Learn how to move all databases associated with 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 2013 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 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.
NoteNote:
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:

To move all databases from one database server to another database server, you have to work in both SharePoint 2013 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 2013 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 2013 farm.

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

    • Scenario 2: Use this procedure if you do not use SharePoint Server 2013 with Service Pack 1 (SP1) and must use manual steps or if you move the databases from a SharePoint Server 2013 single server installation to a new single server installation.

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

To prepare the new database server 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 the SharePoint Central Administration website, in the Services snap-in, stop the following services:

    • SharePoint 2013 Administration

    • SharePoint 2013 Timer

    • SharePoint 2013 Tracing

    • SharePoint 2013 User Code Host

    • SharePoint 2013 VSS Writer

    • World Wide Web Publishing Service

    • SharePoint Server Search

  2. On the server that is running the SharePoint Central Administration website, 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 SQL Server 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 Knowledge Base article 918992: How to transfer the logins and the passwords between instances of SQL Server 2005.

    For more information about how to transfer SQL Server metadata between instances, see the SQL Server Books Online article 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 How to: Attach a Database (SQL Server Management Studio) 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 2013 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 2013 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 Client Network Utility, (cliconfg.exe). This utility is typically located in the C:\Windows\System32 folder on Windows Server 2008 R2 Service Pack 1 (SP1) and Windows Server 2012.

  4. On the General tab, verify that TCP/IP is enabled.

  5. On the Alias tab, click Add. The Add Network Library Configuration dialog box appears.

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

  7. In the Network libraries area, click TCP/IP.

  8. In the Connection parameters area, in the Server name box, enter the new server name and instance to associate with the alias, and then click OK. This is the name of the new server that is hosting the SharePoint 2013 databases.

  9. Repeat steps 3 through 8 on all servers in the farm that connect to the new instance of SQL Server.

  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 How to: Install SQL Server 2008 R2 (Setup).

ImportantImportant:
These Windows PowerShell commands, were introduced in SharePoint Server 2013 Service Pack 1 (SP1):
  • Add-DatabaseToAvailabilityGroup

  • Remove-DatabaseFromAvailabilityGroup

  • Get-AvailabilityGroupStatus

Use the following procedure to update the database connections if you use SharePoint Server 2013 with Service Pack 1 (SP1) and SQL Server AlwaysOn Availability Groups for high availability or disaster recovery.

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

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

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

  3. At the Windows 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:

  • If you do not use SharePoint Server 2013 with Service Pack 1 (SP1) but must use manual steps

  • If you move the databases from a SharePoint Server 2013 single server installation to a new single server installation

  • If you use Availability Groups then you must manually add the databases to the availability groups as appropriate to their high availability/disaster recovery support. For more information, see Add a Database to an Availability Group (SQL Server)

  • If you use SQL Mirroring then make sure your mirroring is setup appropriately. For more information, see Setting Up Database Mirroring (SQL Server) and Database Mirroring (SQL Server).

Scenario 2: To update the database connections by using Windows PowerShell
  1. Start the SharePoint 2013 Management Shell.

  2. At the Windows 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 2013 and IIS. These include the following services:

    • SharePoint 2013 Administration

    • SharePoint 2013 Timer

    • SharePoint 2013 Tracing

    • SharePoint 2013 User Code Host

    • SharePoint 2013 VSS Writer

    • World Wide Web Publishing Service

    • SharePoint Server Search

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft