Upgrade your Internet Experience
Microsoft.com
Welcome Sign in
SharePoint Server TechCenter
Click to Rate and Give Feedback
TechNet
TechNet Library
Office
Operations
 Move all databases

  Switch on low bandwidth view
Move all databases (Office SharePoint Server 2007)

Updated: 2009-06-11

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:

ImportantImportant:

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

    ImportantImportant:

    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.

    ImportantImportant:

    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.

ImportantImportant:

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

      NoteNote:

      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 (http://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.

    ImportantImportant:

    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 (http://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 (http://go.microsoft.com/fwlink/?LinkId=123469&clcid=0x409).

    ImportantImportant:

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

    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.

  • For more information about how to upgrade to Windows Server 2008, see Installing Windows Server 2008 (http://go.microsoft.com/fwlink/?LinkId=140803) or see the documentation that is included with the software.

  • For more information about how to upgrade to Microsoft SQL Server 2008, see Installing SQL Server 2008 (http://go.microsoft.com/fwlink/?LinkID=141021) or see the documentation that is included with the software.

ImportantImportant:

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.

ImportantImportant:

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

Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
Missing information      LordFox1973 ... Thomas Lee   |   Edit   |   Show History
The procedure overview item 2 (Remove all SSPs from the farm) is not described in more detail further on in this document.
Issues with multiple server environment      Scott at Work   |   Edit   |   Show History
I followed this process for a single server setup (installed using Custom & choosing web farm), and was successful in moving the Configuration & Admin databases to a new SQL server.

When I tried this process with a multiple server farm (Dev environment), SharePoint would not connect to the databases on the new SQL cluster. I could migrate the Content databases by doing a backup & restore to the new SQL Cluster, but the Configuration and Administration databases would not be recognized on the new SQL cluster.

Now, when I try to use the renameserver command again, SharePoint tells me that the old SQL server is not part of the Farm, but the Farm is still connecting to the old SQL server.

Very frustrating.


It Works      Marino Valentino   |   Edit   |   Show History
... but I had to migrate the SQL Users manually.
Tags What's this?: Add a tag
Flag as ContentBug
It didn't work!      jim®   |   Edit   |   Show History
I followed these directions to the "T" on a Single Server Environment where I wanted to move the databases to an SQL 2008 Server, everything worked liked a charm till I got to the Restore SSP item, when I attempted to visit the SharePoint CA I receive the error "Cannot connect to the configuration database"
Tags What's this?: Add a tag
Flag as ContentBug
Updated content      LHaapanen - MSFT   |   Edit   |   Show History
The TechNet archive is undergoing some organizational changes behind the scenes right now and for the next few weeks, User Assistance members aren’t able to make changes to the content. We haven’t stopped developing and refining the articles and procedures there, however, and because the Move All Databases procedure is important and affects many of our customers, we want to make it available as widely and as quickly as possible. That’s why we’re temporarily offering updated procedures for Office SharePoint Server 2007 here: "Moving all databases to a different database server or a server with the same name" (http://go.microsoft.com/fwlink/?LinkId=141259). When we’re able to publish to TechNet again, we’ll update all Move All Databases topics with the new content. Thanks.

4/10/09: This content is now updated. Thanks for your patience.
Tags What's this?: Add a tag
Flag as ContentBug
When in the process do you take the old SQL Databases off line?      Norm Smith   |   Edit   |   Show History
Trying to follow this process to move Config DB and CA DBs to a new SQL server, but not sure when to take the ones on the old server off line.
Tags What's this?: Add a tag
Flag as ContentBug
What about restoring corrupted databases to the existing SQL server?      avincent   |   Edit   |   Show History
In reading the white paper: Data Protection and recovery for Microsoft Office SharePoint Server 2007 ( September 2008), it indicates that the restoration of the configuration database is not supported using SharePoint tools and refers the reader to this article here. However, this article only talks about moving the databases to a new SQL server. Where are the instructions to restore a content database to the same SQL server it originates on?
Restoring the backup didnt work      Molayyan   |   Edit   |   Show History
Hi there
i am planing to migrate all MOSS 2007 DBs to another server, so far i've been testing a test environment, i successfully followed the point except point 13. b .
i alwayes get the error :
The farm is not available at the command window after restorin the SSP
command line as follow:

stsadm -o retore -directory "AppSrv" -backupid "XXXXXXXXXXXXXXXXXXXXXXXXXX" restoremethod new -newdatabaseserver DBSRV2
How about dealing with a Named Instance of SQL?      jj_the_Skeptic   |   Edit   |   Show History
The article states plainly that you can't use stsadm -o renameserver with a named instance of SQL - I could really use an alternate procedure or manual steps for that scenario. I'm trying to move from a named instance of SQL server to a non-named instance.
Tags What's this?: Add a tag
Flag as ContentBug
Procedure for Named Instances Doesn't Work      Andrew Wolfe   |   Edit   |   Show History
I did the DB Backups and Restores moving from SSEE to a named Full SQL Instance on another Server. This was SharePoint Services so no SSPs to worry about. I then created Aliases based on Named Pipes as this is what SharePoint was using. I stoped the SSEE instance and used SQL Management studio to prove that I now got connected to the new SQL via the alias. However SharePoint was never able to find its configuration database and I was not able to complete the procedure.

In the end I went for Creating a new farm, redoing the farm configuration and PDF breaking, recreating my web app and then attaching my Content DBs

Please Microsoft give us a Procedure that actually works
Tags What's this?: Add a tag
Flag as ContentBug
Creating SQL Connection Alias should make the entire process transparent      Camo90   |   Edit   |   Show History
If you create a SQL connection alias that points the original SQL instance to the new SQL instance, this should make the entire move of databases transparent to sharepoint. You should not even have to backup, delete ssp and restore the SSP and index.
Tags What's this?: Add a tag
Flag as ContentBug
© 2009 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks | Privacy Statement
Page view tracker