Export (0) Print
Expand All
7 out of 16 rated this helpful - Rate this topic

Move all databases in SharePoint 2013

 

Applies to: SharePoint Server 2013, SharePoint Foundation 2013

Topic Last Modified: 2014-02-27

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

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.

  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.

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

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

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

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