Share via

Move all databases (Windows SharePoint Services 3.0)

  • Article

Applies To: Windows SharePoint Services 3.0

 

Topic Last Modified: 2009-07-10

This article contains information and steps for moving all the databases associated with Windows SharePoint Services 3.0 from one database server to another database server. This article includes two different procedures for moving databases:

  • To move all databases to a database server that has a different name, use the Move all databases to a different database server procedure.

  • To move all databases to an identically named or upgraded database server, use the Move all databases to a database server that has the same name procedure.

Important

The procedures in this article require you to stop the complete farm. This will make all farm sites and assets unavailable to users until the process is complete.

The following databases can be moved by using the procedures in this article:

  • Content databases

  • Search database

  • Central Administration content database

  • Configuration database

Move all databases to a different database server

This is a procedure for moving all the databases in a Windows SharePoint Services 3.0 farm to a new database server that has a different name. The new database server must use the same version of Windows Server and SQL Server as the old database server. To move the databases to an identically named or upgraded server, use the Move all databases to an upgraded database server procedure.

The procedures that follow use several different Windows SharePoint Services 3.0 and Microsoft SQL Server 2005 or SQL Server 2008 built-in tools such as the Central Administration Web site and the Stsadm command-line tool. The process of moving all the databases from one database server to another database server includes the following steps:

  1. Prepare the new database server.

  2. Stop the farm. Before you can move all the databases, you must stop the farm to ensure that no changes are made while the databases are moved.

  3. Back up the following databases:

    • Content databases

    • Configuration database

    • Central Administration content database

    • Windows SharePoint Services Help Search database

  4. Copy or move the database backup files to the destination database server.

  5. Restore the databases.

  6. Copy to the destination database server all the SQL Server logins, fixed server roles, fixed database roles, and permissions for these databases.

  7. Redirect the farm to reference the new database server.

    Important

    We recommend that you direct the farm to the new database server by using a SQL Server connection alias. You must create a SQL Server connection alias that can be used by all servers (including the front-end Web servers and application servers) to connect to the instance of SQL Server. A connection alias is an alternative name that you can use to make a connection to an instance of SQL Server.

  8. Restart the server that is running Central Administration to apply the changes and ensure that the services, Web sites, and application pools that are associated with Windows SharePoint Services 3.0 are started.

Important

The following are the minimum required to perform this procedure:

  • 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 Administrators group.

    • You must be a member of the db_backupoperator fixed database role.

  • On the database server to which the databases are being moved:

    • You must be a member of the Administrators group.

    • You must be a member of the db_owner fixed database role.

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

Move all databases to a different database server

  1. Prepare the new database server by using the Prepare the database servers procedure.

  2. Stop the farm by performing the following steps:

    1. On the server that is running the Central Administration Web site, in the Services snap-in, stop the following services:

      • Windows SharePoint Services Administration service

      • Windows SharePoint Services Search service

      • Windows SharePoint Services Timer service

      • Windows SharePoint Services Tracing service

      • Windows SharePoint Services VSS Writer service

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

    3. Repeat step 6 on each server in the farm.

  3. Back up the databases on the source database server.

    1. Start SQL Server Management Studio and connect to the database server.

    2. In Object Explorer, expand Databases.

    3. Right-click the configuration database (usually named SharePoint_Config), point to Tasks, and then click Back Up.

    4. In the Back Up Database dialog box, in the Source area, select the type of backup that you want to perform from the Backup type list. For more information about which backup type to use, see Overview of Recovery Models (https://go.microsoft.com/fwlink/?LinkId=114396&clcid=0x409) in the SQL Server 2005 Books Online.

    5. Click Database.

    6. In the Backup set area, in the Name text box, type a name or use the default.

    7. In the Description text box, type a description of the backup.

    8. Specify how long the backup should be kept, or use the default. When the backup set expires, the backup set can be overwritten by any subsequent backups that have the same name. By default, the setting for the backup set is 0 days so that it never expires.

    9. In the Destination section, specify a location to store the backup set, or use the default.

    10. Click OK to back up the database.

    11. Repeat steps c through j for the remaining databases in the farm.

  4. In Windows Explorer, locate the database backup (.bak) files that you want to move, and then copy or move them to the destination server.

    Important

    In some environments, this step must be performed by the database administrator.
    Move only the backup files to the destination database server. Do not move any databases or other files at this time.

  5. Restore databases on the destination database server.

    1. Start SQL Server Management Studio and connect to the database server.

    2. In Object Explorer, expand Databases.

    3. Right-click the database that you want to restore, point to Tasks, point to Restore, and then click Database.

    4. In the Restore Database dialog box, specify the destination and the source, and then select the backup set or sets that you want to restore.

      The default values for destination and source typically suit most recovery scenarios.

    5. In the Select a page pane, click Options.

    6. In the Restore options section, select only Overwrite the existing database. Unless your environment or policies require otherwise, do not select the other options in this section.

    7. In the Recovery state section:

      • If you have included all the transaction logs that you must restore, select RESTORE WITH RECOVERY.

      • If you must restore additional transaction logs, select RESTORE WITH NORECOVERY.

      • The third option, RESTORE WITH STANDBY, is not used in this scenario.

    8. Click OK to complete the restore operation.

    9. Repeat steps c through h for each database that you are restoring.

  6. Use SQL Server to copy to the destination server the logons for all service accounts, including SQL Server logins, fixed server roles, fixed database roles, and permissions for the databases.

    For information about how to copy SQL Server roles and permissions to the destination database server, see Microsoft Help and Support article 246133, How to transfer logins and passwords between instances of SQL Server (https://go.microsoft.com/fwlink/?LinkId=114420&clcid=0x409). For troubleshooting information, see Microsoft Help and Support article 240872, How to resolve permission issues when you move a database between servers that are running SQL Server (https://go.microsoft.com/fwlink/?LinkId=123469&clcid=0x409).

    Important

    In some environments, this step must be performed by the database administrator.

  7. Refer the farm to the new database server by creating a SQL Server connection alias.

    1. Start the SQL Server Native Client Network Utility (%SYSTEM%\cliconfg.exe).

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

    3. On the Alias tab, click Add.

      The Add Network Library Configuration dialog box appears.

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

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

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

    7. Repeat steps a through f on all servers that connect to SQL Server.

  8. Start the new farm by performing the following steps:

    1. In the Services snap-in, start the following services:

      • Windows SharePoint Services Administration service

      • Windows SharePoint Services Search service

      • Windows SharePoint Services Timer service

      • Windows SharePoint Services Tracing service

      • Windows SharePoint Services VSS Writer service (optional)

    2. At the command prompt, type iisreset /start.

    3. Repeat steps a and b on each server in the farm.

Move all databases to a database server that has the same name

This is a procedure for moving all Windows SharePoint Services 3.0 databases to an identically named database server. The new database server can differ in the following ways from the original server:

  • It runs a newer version of SQL Server—for example, SQL Server 2008 instead of SQL Server 2005.

  • It runs a newer version of Windows—for example, Windows Server 2008 instead of Windows Server 2003.

  • It is a 64-bit computer that runs a 64-bit operating system, instead of a 32-bit computer that runs a 32-bit operating system.

This article does not provide information about how to upgrade the database server.

Important

For information about how to migrate the current database server, see the Migrate an existing server farm to a 64-bit environment (Windows SharePoint Services 3.0) procedure.

This procedure uses several different Windows SharePoint Services 3.0 and SQL Server built-in tools such as the Central Administration Web site, SQL Server Management Studio or SQL Server Management Studio Express (SSMSE), and the Stsadm command-line tool. The process of moving databases to a new database server includes the following steps:

  1. Stop the farm.

  2. Detach the databases.

  3. Shut down the old database server.

  4. Prepare the new database server that has the same name as the old one.

  5. Reattach the databases on the new server.

  6. Restart the server that is running Central Administration to apply the changes and ensure that the services, Web sites, and application pools that are associated with Windows SharePoint Services 3.0 are started.

Important

The following are the minimum required to perform this procedure:

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

  • If you are using Microsoft SQL Server 2005 Express Edition, you must have installed SQL Server Management Studio Express (SSMSE). For more information about how to install SSMSE, visit the SSMSE download site (https://go.microsoft.com/fwlink/?LinkId=140832 ).

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

  • On the new and old database servers:

    • You must be a member of the Administrators group.

    • You must be a member of the db_owner fixed database role.

  • In some environments, you must coordinate the migration procedures with the database administrator. Be sure to follow any applicable policies and guidelines for handling databases.

Move all databases to a database server that has the same name

  1. Stop the farm by following these steps:

    1. On the server that is running the Central Administration Web site, in the Services snap-in, stop the following services:

      • Windows SharePoint Services Administration service

      • Windows SharePoint Services Search service

      • Windows SharePoint Services Timer service

      • Windows SharePoint Services Tracing service

      • Windows SharePoint Services VSS Writer service

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

  2. Detach the databases on the source database server by following these steps:

    1. Start SSMSE and connect to the database server.

    2. In Object Explorer, expand Databases.

    3. Right-click the database that you want to detach, point to Tasks, and then click Detach.

    4. In the Detach Database dialog box, select the database that you want to detach.

    5. Click OK.

    6. Repeat these steps for each database in the Windows SharePoint Services 3.0 deployment.

  3. To prevent loss of data during the move, you can back up the databases on the source database server by using the Back up databases (Windows SharePoint Services 3.0) procedure.

  4. Shut down the original database server.

  5. Prepare a new database server that has the same name as the original database server by using the Prepare the database servers procedure. In addition, you can make improvements to the new database server by installing the following software:

  6. Reattach the databases to the new database server by performing the following steps:

    1. Start SSMSE and connect to the database server.

    2. In Object Explorer, expand Databases.

    3. Right-click Databases, point to Tasks, and then click Attach.

    4. In the Attach Databases dialog box, in the Databases to attach section, click Add.

      1. In the Locate Database Files dialog box, find the databases that you want to attach.

      2. Click a database and then click OK.

      3. Repeat this step for each database that you want to attach.

    5. Click OK.

    6. To make the databases that you attached appear in the Object Explorer pane, refresh the view.

  7. Restart the farm by performing the following steps:

    1. On the server that is assigned to run the Central Administration Web site, in the Services snap-in, start the following services:

      • Windows SharePoint Services Administration service

      • Windows SharePoint Services Search service

      • Windows SharePoint Services Timer service

      • Windows SharePoint Services Tracing service

      • Windows SharePoint Services VSS Writer service (optional)

    2. On the server that is assigned to run the Central Administration Web site, in the Internet Information Services (IIS) Manager snap-in, start the SharePoint Central Administration v3 Web service.

See Also

Concepts

Add, split, and merge content databases (Windows SharePoint Services 3.0)
Move content databases (Windows SharePoint Services 3.0)
Move and migrate servers and server farms (Windows SharePoint Services 3.0)
Migrate content databases from Windows Internal Database to an instance of SQL Server (Windows SharePoint Services 3.0)