Sharepoint
United States (English) Sign in
Home Online 2013 2010 Other Versions Related Products Library Forums Gallery
4 out of 7 rated this helpful - Rate this topic

Move all databases in SharePoint 2013

SharePoint 2013

Published: July 16, 2012

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

Applies to:  SharePoint Foundation 2013 | SharePoint Server 2013 

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.

Before you begin

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.

Important Important:

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.

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:

Move all databases

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.

    note Note:

    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. 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 Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.

    note Note:

    If SQL Server Configuration Manager is not installed, you must run SQL Server setup to install it.

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

  4. In the Alias Name field, enter the name of the original SQL Server instance, for Protocol, verify that TCP/IP is selected, for Server, enter the name of the new server that is hosting the SharePoint 2013 databases, and then click OK.

  5. Repeat this procedure on each database server.

  6. 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)
© 2013 Microsoft
|
Site Feedback
© 2013 Microsoft. All rights reserved.