Advanced Restoring of Microsoft Project Server 2010 Databases
As many of you already know, restoring a Project Server 2010 database can be done a few ways:
- SharePoint Central Administration
- SQL Server Management Studio
And this is all fine if you are restoring the database from the same server.
But what happens if you attempt to restore the databases from a different SharePoint/SQL Server and possibly from a different domain? How about working in an isolated Dev environment and wanting to restore the database into production? While this is a very unlikely scenario in a production environment, nevertheless what would happen if you had to rebuild your network domain infrastructure?
Here’s a hint: we know that Active Directory GUIDs are used for Project Server users to access PWA, so if the GUIDs are changed in any way there will be no access to PWA and would receive the following error (that includes the Administrator):
Error: Access Denied
Current User
You are currently signed in as: TRIMAGNA\Administrator
So let’s step back and go through the steps to first restore the Project Server databases. We’ll use a SQL Query script to restore the databases as we will need to change the User Mode of each database to Single_User and it’s a lot easier doing this from a script.
Restoring Project Server Database with SQL Query Script
1. Launch the SQL Server Management Studio console.
2. From the menu bar, click New Query.
3. Type the following code for each of the 5 project databases and click Execute:
USE MASTER;
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE <database name> FROM DISK = <physical location with filename> WITH REPLACE;
GO
For Example:
USE MASTER;
ALTER DATABASE ProjectServer_Archive SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE ProjectServer_Archive FROM DISK = ‘C:\Backup\ProjectServer_Archive.BAK‘ WITH REPLACE;
GO
USE MASTER;
ALTER DATABASE ProjectServer_Draft SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE ProjectServer_Draft FROM DISK = ‘C:\Backup\ProjectServer_Draft.BAK‘ WITH REPLACE;
GO
USE MASTER;
ALTER DATABASE ProjectServer_Published SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE ProjectServer_Published FROM DISK = ‘C:\Backup\ProjectServer_Published.BAK‘ WITH REPLACE;
GO
USE MASTER;
ALTER DATABASE ProjectServer_Reporting SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE ProjectServer_Reporting FROM DISK = ‘C:\Backup\ProjectServer_Reporting.BAK‘ WITH REPLACE;
GO
USE MASTER;
ALTER DATABASE ProjectServer_WSS_CONTENT SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE ProjectServer_WSS_CONTENT FROM DISK = ‘C:\Backup\ProjectServer_WSS_Content.BAK‘ WITH REPLACE;
GO
USE 4. When complete, make sure to review the Messages pane for any errors.
5. From the menu bar, click New Query.
6. Type the following code for each of the 5 project databases and click Execute:
USE MASTER;
ALTER DATABASE <database name> SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO
For Example:
USE MASTER;
ALTER DATABASE ProjectServer_Archive SET MULTI_USER;
ALTER DATABASE ProjectServer_Draft SET MULTI_USER;
ALTER DATABASE ProjectServer_Published SET MULTI_USER;
ALTER DATABASE ProjectServer_Reporting SET MULTI_USER;
ALTER DATABASE ProjectServer_WSS_Content SET MULTI_USER;
GO
This will set the databases back to Multi_User mode.
Resetting the Database Permissions
In this exercise we will be resetting the permissions to the Project Server databases with a New Service Account mapped in SQL Server. The reason for using a new service account is that the old account used as the Farm Service Account is mapped using the old Domain GUID. So although we recreated and restored everything just as it was before, the GUIDs will be different. To minimize cross contamination, we create a new account and associate it with the databases and SharePoint.
1. Create a New Farm Account in Active Directory Users and Computers console. (Mine is called FARMservice)
2. In the Object Explorer pane, expand Security, right click Logins, and click New Login.
3. In the Login Properties dialog box, next to the Login name, click Search.
4. In the Select User or Group dialog box, change the From this location to Entire Directory, in the Object Name box, type the new Farm Service Account name (Remember to include the domain name)
5. In the Login – New dialog box, in the Select a page pane, click User Mapping
6. Under Users mapped to this login section, select the first Project Server database check box and then under Database role membership for section, ensure that the public and db_owner check boxes are selected. Repeat this step for each Project Server database and then click OK.
7. Minimize SQL Server Management Studio console.
Removing the Current PWA Instance
In this exercise we will be removing the current PWA Instance, but will not be deleting the Site Collection. Later in the process we will be adding the PWA instance back. (obviously)
1. Launch Internet Explorer.and open SharePoint Central Administration site.
2. From the Central Administration Home page, under Application Management section, click Manage service applications.
3. On the Manage Service Applications page, click Project Server Service Application.
4. On the Manage Project Web App Sites page, hover the cursor over the PWA URL, click the down arrow and click Delete.
5. On the Delete Project Web App Site page, clear the Delete site collection from SharePoint check box, and then click Delete.
6. On the Manage Project Web App Sites page, wait until the PWA URL has been deleted before proceeding to the next exercise.
Resetting the SharePoint Service Account
In this exercise we will be changing the current SharePoint service account to the new Farm Service account.
1. From the Quick Launch, click Security.
2. On the Security page, under General Security section, click Configure service accounts.
3. On the Service Accounts page, click Register new managed account link.
4. On the Register Managed Account page, in the User name box, type the new Farm Service Account name, in the Password box, type the password and click OK.
5. Open a Command Prompt and navigate to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN
6. Type the following command:
stsadm -o updatefarmcredentials –userlogin “<new Farm Service Account>” -password Pa$$w0rd
For example:
stsadm -o updatefarmcredentials –userlogin TRIMAGNA\FARMservice -password Pa$$w0rd
NOTE: This will take a few minutes to complete
7. When complete, type IISRESET.
8. When complete, close the Command Prompt.
9. Back to Internet Explorer, press F5 to refresh the web page.
10. From the Service Accounts page, click Select one… and select Farm Account, in the Select an account for this component list, verify that the new Farm Service Account name is listed as the new account,
11. Repeat the previous step replacing any of the components using the old service account with the new Farm Service Account. In my case, that’s the following components:
- Windows Service – Microsoft Project Server Events Service
- Windows Service – Microsoft Project Server Queuing Service
- Windows Service – Microsoft SharePoint Foundation Sandboxed Code Service
- Windows Service – SharePoint Server Search
- Windows Service – User Profile Synchronization Service
- Windows Service – Web Analytics Data Processing Service
- Web Application Pool – ProjectWebAppPool
- Security Token Service Application (Security Token Service Application)
- Service Application Pool – SharePoint Web Services Default
- Service Application Pool – SharePoint Web Services System
Resetting the Web Server
1. Launch a Command Prompt and type IISRESET and press Enter.
2. Close the Command Prompt when finished.
Resetting the Project Site Database
In this exercise we will be removing the old association of the Project Site database and replace it with a new association of the new database.
1. From the Quick Launch, click Application Management.
2. On the Application Management page, under Databases section, click Manage content databases.
3. On the Manage Content Databases page, ensure you are working on the correct Web Application that is hosting Project Site and then click the Database Name.
4. On the Manage Content Database Settings page, select the Remove content database check box and click OK.
NOTE: This will not delete the database from SQL Server, but only remove the association from the Web Application.
5. On the Manage Content Databases page, click Add a content database.
6. On the Add Content Database page, ensure you are working on the correct Web Application that is hosting Project Site, in the Database Name box, type the name of the restored database and click OK..
OPTIONAL
If you have site collection in the same web application as your PWA site, you will need to delete it and then add it back again. Do this procedure only if you have a site collection backup or do not have any data in the site collection.
7. On the Application Management page, under Site Collections, click Delete a site collection.
8. On the Delete Site Collection page, in the Site Collection list, click Change Site Collection.
9. In my environment, I have 2 Site Collections in the Web Application. In my case I’m selecting the root [ / ] and click OK.
10. On the Delete Site Collection page, click Delete.
11. On the Application Management page, under Site Collections, click Create site collections.
12. On the Create Site Collection page, complete the settings, but make sure you use the Domain Administrator account for the Primary Site Collection Administrator and the New Farm Service Account for the Secondary Site Collection Administrator.
13. On the Top-Level Site Successfully Created page, click the URL to open the site collection in a new window, Optional: Restore the site collection to the new site collection.
14. Close the window.
15. Switch back to the Top-Level Site Successfully Created page, click OK.
Removing the Active Directory GUIDs for all Project Server Users
Currently the restored Project Server database has the AD GUIDs from the previous AD Domain. We need to remove these GUIDs to NULL in the Project Server Published database. In this exercise we will be removing the Active Directory GUIDs for all Project Server Users using a SQL Query script.
1. Maximize the SQL Server Management Studio console.
2. In the Object Explorer pane, expand Databases –> ProjectServer_Published –> Tables –> dbo.MSP_RESOURCES.
3. Right click dbo.MSP_RESOURCES and click Select Top 1000 Rows.
4. In the Results pane, review the data in the table, specifically take note of the column named WRES_AD_GUID
5. From the menu bar, click New Query.
6. Type the following code and click Execute:
USE ProjectServer_Published
UPDATE MSP_RESOURCES SET WRES_AD_GUID = NULL
GO
7. Repeat steps 2-4. You should notice that the WRES_AD_GUID data now display NULL.
8. In the Object Explorer pane, expand Security –> Logins, right click the New Farm Service Account and click Properties.
9. In the Login Properties dialog box, in the Select a page pane, click User Mapping.
10. Under Users mapped to this login section, select the first Project Server database check box and then under Database role membership for section, ensure that the public and db_owner check boxes are selected. Repeat this step for each Project Server database and then click OK.
11. Minimize the SQL Server Management Studio console.
Adding Permissions to Web Application for PWA
Prior to creating the PWA Instance, we need to add the Full Control and Full Read permissions to the Web Application that will be hosting PWA.
1. Switch back to Internet Explorer and the Application Management page, under Web Applications, click Manage web applications.
2. On the Web Application Management page, select the Project Web Application and from the ribbon, click User Policy.
3. In the Policy for Web Application dialog box, click Add Users.
4. In the Add Users dialog box, click Next,
5 In the Add Users dialog box, in the Users box, type TRIMAGNA\Administrator, under Permissions section, select Full Control and Full Read checkboxes and click Finish.
6. In the Policy for Web Application dialog box, click OK.
Adding the PWA Instance
Now that the restored Project Server Published database has all the AD GUIDs removed. it’s time to add back the PWA Instance, however we’ll be using the New Farm Service Account as the Administrator Account.
1. From the Quick Launch, click Application Management.
2. On the Application Management page, under Service Applications, click Manage service applications.
3. On the Manage Service Applications page, click Project Server Service Application.
4. On the Manage Project Web App Sites page, click Create Project Web App Site.
5. On the Create Project Web App Site page, change the Administrator Account to the New Farm Service Account, accept the defaults and click OK.
6. On the Manage Project Web App Sites page, when the Status has changed to Provisioned, click on the PWA URL.
7. You should receive the following error message:
This is normal as the new Farm Service Account is the Project Server Administrator.
8. Click Sign in as a different user and log in as the new Farm Service Account.
9. The PWA home page is now displayed
Re-Synchronizing Enterprise Resource Pool with Active Directory
Now that we have access to PWA again, we need to replace the NULL value in the WRES_AD_GUID column with the new GUIDs in the new domain. The simplest way to do this is re-synchronize the Enterprise Resource Pool with Active Directory.
1. From the Quick Launch, click Server Settings.
2. On the Server Settings page, under Security section, click Manage Users.
3. On the Manage Users page, click on Administrator.
4. On the Edit User: Administrator page, scroll to the bottom of the page and expand System Identification Data.
Notice that there is no Active Directory GUID as the value is currently set to NULL
5. Click Cancel.
6. From the Quick Launch, click Server Settings.
7. On the Server Settings page, under Operational Policies section, click Active Directory Resource Pool Synchronization.
8. On the Active Directory Enterprise Resource Pool Synchronization page, click Find Group.
9. In the Find Group in Active Directory dialog box, in the Group Name box, type the name of an Active Directory group name that will resync all the Project Server users that are also Enterprise Resources. Click the green arrow, select the group name from the list and click OK
In my case I created an AD Global Security Group called TRIMAGNA Users in Active Directory Users and Computers and added all my Project Server users as a member of this group.
10 On the Active Directory Enterprise Resource Pool Synchronization page, click Save and Synchronize Now.
11. Wait a few minutes for the re-sync to complete.
12. On the Server Settings page, under Security section, click Manage Users.
13. On the Manage Users page, click on the Administrator account.
14. On the Edit User: Administrator page, scroll to the bottom of the page and expand System Identification Data.
Notice that the Active Directory GUID now has a value.
15. Click Cancel.
OPTIONAL
Re-Synchronizing Project Server Groups with Active Directory
Now that the Project Server user accounts have been re-synchronized this would be a good time to re-sync the Project Server Groups with Active Directory.
1. From the Quick Launch, click Server Settings.
2. On the Server Settings page, under Security section, click Manage Groups.
3. On the Manage Groups page, click on a Project Server group name to synchronize.
4. On the Add or Edit Group page, under Group Information section, click Find Group.
5. In the Find Group in Active Directory dialog box, in the Group Name box, type the name of an Active Directory group name that will resync with the current Project Server group . Click the green arrow, select the group name from the list and click OK.
6. On the Add or Edit Group page, click Save.
7. Repeat Steps 3-6 for each Project Server group to re-synchronize.
8. On the Manage Groups page, on the menu bar, click Active Directory Sync Options.
9. In the Active Directory Sync Options dialog box, click Save and Synchronize Now.
10. Wait a few minutes for the re-sync to complete, then press F5 key to refresh.
11. The Last Sync column should display Succeeded.
Resetting PWA Site Permissions for Administrator
Now the only thing left to do is test if we can now access PWA as the Administrator.
1. From the Site Actions menu, click Site Permissions.
2. On the Permissions: Project Web App page, select the checkbox for Administrator, then on the ribbon menu, click Remove User Permissions.
3. On the ribbon menu, click Grant Permissions.
4. In the Grant Permissions dialog box, In the Users/Groups box, type TRIMAGNA\Administrator, select Grant users permissions directly, select Full Control check box and click OK.
5. On the ribbon menu, click Site Collection Administrators.
6. In the Site Collection Administrators dialog box, replace all user accounts with the Administrator and New Farm Service Account, click Check Names icon and click OK.
7. On the Permissions: Project Web App page, click Browse tab and click Project Web App to return to the PWA Home page.
Testing Access to PWA as Administrator
Now the only thing left to do is test if we can now access PWA as the Administrator.
1. From the Personal menu, click System Account and click Sign in as Different User,
2. In the Windows Security window, type the Administrator account (remember to include the domain name) with the password and click OK.
3. The Project Web App home page is now displayed.
4. To verify that the account has Administrator permissions, from the Quick Launch, click Server Settings. If all the Administrator functions are displayed the account has Project Server Administrator permissions.
