Move all databases (Project Server 2010)
Applies to: Project Server 2010
Topic Last Modified: 2011-09-10
This article contains information and steps to move all of the databases associated with Microsoft Project Server 2010 from one database server to another database server within the same farm.
Important
This task requires you to stop the entire farm. This will make all farm sites and assets unavailable to users until the process is complete.
This article does not provide information about how to migrate or move the Project Server 2010 farm from one group of servers to another group of servers. For more information about how to move the complete farm from one set of farm servers to another, see Migrate a Project Web App instance in Project Server 2010.
This article does not provide information about how to upgrade SQL Server 2008 on the database server. For more information about how to upgrade SQL Server 2008, see Upgrading to SQL Server 2008 (https://go.microsoft.com/fwlink/p/?LinkId=188747) in SQL Server 2008 Books Online.
You must move the following databases associated with Project Server 2010:
Draft database
Published database
Archive database
Reporting database
Content database
The following are required to perform the procedures for this task:
You must be logged on to the server that is running the SharePoint Central Administration Web site.
You must have membership in the following roles in SQL Server:
db_backupoperator fixed database role to back up the databases.
dbcreator fixed server role on the destination server that is running SQL Server or an instance of SQL Server to restore the databases.
You must coordinate the move procedures with the database administrator (DBA). Steps that require the involvement of the DBA are signified by the prefix "[DBA]".
Procedure overview
This section summarizes the procedures that you must follow to complete the move process for all the databases associated with Project Server 2010. To see the actual procedure steps, see each procedure following the overview.
In the move process, you will use Project Server 2010 tools and SQL Server 2005 or SQL Server 2008 tools. Moving all of the databases from one database server to another database server consists of the following steps:
(In some environments, the database administrator must perform certain steps. Those steps begin with "[DBA]".)
Perform a full backup of the default Shared Services Application (SSP).
Completely stop the farm by stopping the services associated with Project Server 2010 and by stopping Internet Information Services (IIS).
[DBA] Back up the following farm databases by using SQL Server tools:
Draft database
Published database
Archive database
Reporting database
Content database
[DBA] Copy or move the backup files to the destination database server.
[DBA] Restore all of the databases on the destination database server by using SQL Server tools.
[DBA] Copy to the destination server all of the SQL Server logins, fixed server roles, fixed database roles, and permissions for these databases.
Backing up databases on the source database server
Important
Membership in the db_backupoperator fixed database role is the minimum required to complete this procedure.
Important
It is important that the queue service is emptied and stopped to prevent data inconsistencies.
In some environments, the DBA must perform these steps.
Back up databases on the source database server
Open Microsoft SQL Server Management Studio and connect to the database server.
In Object Explorer, expand Databases.
Right-click the database that you want to back up, point to Tasks, and then click Back Up.
In the Back Up Database dialog box, 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 the Recovery Models (https://go.microsoft.com/fwlink/p/?LinkId=114396) in SQL Server 2008 Books Online.
Under Backup component, select the Database option.
In the Backup Set section, either use the default name provided or specify a name for the backup set in the Name text box.
Specify the expiration date for the backup set. This determines how long, or when, the backup set can be overwritten by any later backups with the same name. By default, the backup set is set to never expire (0 days).
In the Destination section, if the correct path of the backup folder is not listed, you can add the path by clicking Add.
Click OK to back up the database.
Repeat steps 3-9 for each database that you want to move.
Important
You should only back up the Draft and Published databases from the source database server.
Copying or moving the backup files to the destination database server
Important
Read access to the shared folder on the source computer and Write access to the shared folder on the destination computer is the minimum required to complete this procedure.
In some environments, the DBA must perform these steps.
Copy or move the backup files to the destination database server
In Windows Explorer, browse to the location of the .bak files for the databases.
Select the .bak files for the databases that you want to move, and then either copy or move them to the destination directory.
Restoring databases on the destination database server
Important
Membership in the dbcreator fixed server role is the minimum required to complete this procedure.
You must add the Farm Administrator account to the dbcreator fixed server role to perform this procedure. The account can be removed from the role after recovery operation is complete.
In some environments, the DBA must perform these steps.
Restore databases on the destination database server
Open Microsoft SQL Server Management Studio and connect to the database server.
In Object Explorer, expand the database instance.
Right-click the Databases node, and then click Restore Database.
In the Restore Database dialog box, under Destination for restore, type the name of the database. You must use the identical name for the database that it had on the source database server.
Under Source for restore, select the From device option.
In the Specify Backup dialog box, select File from the Backup media list.
Click Add, and then browse to the .bak file for the database.
Select the .bak file, and then click OK. Click OK again to add the path of the Restore Backup dialog box.
Ensure that the backup set is selected in the Select backup sets to restore list.
In the Select a page pane, click Options.
In the Restore options section, select only Overwrite the existing database. Unless the environment or policies require otherwise, do not select the other options in this section.
In the Recovery state section, select the RESTORE WITH RECOVERY option.
For more information about these recovery options, see Restore Database (Options Page) (https://go.microsoft.com/fwlink/p/?LinkId=114420).
Click OK to complete the recovery operation.
Repeat steps 3-13 for each database that you are moving.
Copying to the destination server all of the SQL Server logins, fixed server roles, fixed database roles, and permissions for these 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/p/?LinkId=132636). 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/p/?LinkId=123469).
Restarting the server
Important
Membership in the Administrators group on the local computer is the minimum required to complete these procedures.
Restart the server
Restart the server that is running Project Server.
In the Services snap-in, ensure that the following services are started:
SharePoint Server Project Server Eventing service
SharePoint Foundation Project Server Queue service
In Internet Information Services (IIS) Manager, ensure that the following Web sites are started:
Office Server Web Services
All SSP Web sites
SharePoint Central Administration