Share via

Move all databases (Office SharePoint Server 2007)

  • Article

Applies To: Office SharePoint Server 2007

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2016-11-14

This article contains information and steps for moving all the databases associated with Microsoft Office SharePoint Server 2007 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:

  • Databases for Shared Services Providers (SSPs)

  • Search databases for SSPs

  • Content databases

  • Search database

  • Central Administration content database

  • Configuration database

Single sign-on (SSO) databases cannot be moved by using the procedures in this article. If you are using SSO, you must move the SSO database separately. For more information, see Back up and restore SSO (Office SharePoint Server 2007).

Move all databases to a different database server

This is a procedure for moving all the databases in a Office SharePoint Server 2007 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 Office SharePoint Server 2007 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. Record which Web applications are associated with the SSPs. This information can be used to reassociate Web applications with the restored SSPs.

  3. Back up the SSPs.

  4. Remove the SSPs from the farm.

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

  6. Back up the following databases:

    • Content databases

    • Central Administration content database

    • Configuration database

    • Windows SharePoint Services Help Search database

    Important

    Single sign-on (SSO) databases cannot be moved by using the procedures in this article. If you are using SSO, you must move the SSO database separately. For more information, see Back up and restore SSO (Office SharePoint Server 2007).

    The other databases in the farm are backed up and restored at the same time as the SSPs.

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

  8. On the destination database server, restore the databases that you backed up.

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

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

  11. Restart the server that is running Central Administration to apply the changes and ensure that the services, Web sites, and application pools associated with Office SharePoint Server 2007 are started.

  12. Restore the SSPs from the backup.

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. Record which Web applications are associated with the SSP by performing the following steps:

    1. On the SharePoint Central Administration Web site, on the Application Management page, in the Office SharePoint Server Shared Services section, click Create or configure this farm’s shared services.

    2. Record the associated Web applications that are listed on the Manage This Farm’s Shared Services page.

  3. Back up an SSP by performing the following steps:

    1. On the drive on which SharePoint Products and Technologies is installed, change to the following directory: %COMMONPROGRAMFILES%\Microsoft shared\Web server extensions\12\Bin.

    2. If you do not already know which node you want to back up, type the following command:

      stsadm -o backup -showtree

      Note

      This command displays the list of objects in the farm that will be backed up, but does not perform a backup. Items that cannot be backed up will be enclosed in brackets ([ ]).

    3. To back up an SSP, type the following command:

      stsadm -o backup -directory <UNC path> -backupmethod full -item <SSP name>

      where UNC path is the UNC path (\\server name\folder name) of the backup folder and where SSP name is the name of the SSP that you want to back up. All databases associated with the SSP will be automatically included in the backup.

    4. Repeat these steps for each SSP that you want to back up.

  4. Remove the SSP by performing the following steps:

    1. On the disk on which Microsoft SharePoint Products and Technologies is installed, change to the following directory: %COMMONPROGRAMFILES%\Microsoft shared\Web server extensions\12\Bin.

    2. To remove an SSP, type the following command, and then press ENTER:

      stsadm -o deletessp -title <SSP name> -deletedatabases -force

      where SSP name is the name of the SSP that you want to remove.

    3. To detach the SSP content database from the SharePoint farm, type the following command, and then press ENTER:

      stsadm -o -deletecontentdb -url <URL> -databasename <database name>

      where URL is the URL of the Web application from which the content database will be detached and database name is the name of the content database to be detached.

    4. Repeat steps b and c for each SSP.

  5. Delete the SSP content databases.

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

    2. In Object Explorer, expand Databases.

    3. Right-click an SSP content database, point to Tasks, and then click Delete.

    4. On the Delete Object page, check that the database you want to delete is highlighted, and then click OK.

    5. Perform steps c and d for each SSP content database.

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

      • Microsoft Single Sign-On service

      • Office Document Conversions Launcher service

      • Office Document Conversions Load Balancer service

      • Office SharePoint Server Search service

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

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

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

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

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

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

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

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

      • Microsoft Single Sign-On service

      • Office Document Conversions Launcher service (optional)

      • Office Document Conversions Load Balancer service (optional)

      • Office SharePoint Server Search service

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

  13. Restore an SSP to the new location.

    1. To obtain the backup GUID for the specific backup that you want to restore, type the following command, and then press ENTER:

      stsadm -o backuphistory -directory <UNC path>

      where UNC path is the path to the shared folder that contains the backup.

    2. To restore a database, type the following command, and then press ENTER:

      stsadm -o restore -directory <UNC path> -restoremethod new -backupid <GUID from backuphistory> -newdatabaseserver <SQL Server instance>

      where UNC path is the UNC path of the backup shared folder, GUID from backuphistory is the GUID for the specific backup package that you want to restore, and SQL Server instance is the name of the SQL Server instance where the SSP databases should be restored.

    Repeat this step for each SSP that you want to move.

  14. Important

    By default, the first SSP to be restored becomes the default. Perform this step if you want to assign a different SSP as the default.

    Configure a restored SSP to be the default SSP:

    1. On the SharePoint Central Administration Web site, on the Application Management page, in the Office SharePoint Server Shared Services section, click Create or configure this farm’s shared services.

    2. On the Manage This Farm’s Shared Services page, click Change Default SSP.

    3. On the Change Default Shared Services Provider page, select the SSP that you restored from the SSP Name list, and then click OK.

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

This is a procedure for moving all Office SharePoint Server 2007 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 (Office SharePoint Server 2007) procedure.

This procedure uses several different Office SharePoint Server 2007 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 Office SharePoint Server 2007 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 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:

      • Microsoft Single Sign-On service

      • Office Document Conversions Launcher service

      • Office Document Conversions Load Balancer service

      • Office SharePoint Server Search service

      • 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 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 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 Office SharePoint Server 2007 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 (Office SharePoint Server) 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 SQL Server Management Studio 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:

      • Microsoft Single Sign-On service

      • Office Document Conversions Launcher service (optional)

      • Office Document Conversions Load Balancer service (optional)

      • Office SharePoint Server Search service

      • 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 following Web services:

      • SharePoint Central Administration v3

      • Office Server Web Services

      • All SSPs

See Also

Concepts

Migrate an existing server farm to a 64-bit environment (Office SharePoint Server 2007)
Move and migrate servers and server farms (Office SharePoint Server 2007)
Migrate content databases from Windows Internal Database or SQL Server Express Edition to an instance of SQL Server (Office SharePoint Server 2007)
Add, split, and merge content databases (Office SharePoint Server 2007)
Move content databases (Office SharePoint Server 2007)