Reconnecting Primavera Database

Recently we had an issue with our Hyper-V clusters which resulted in some of our VM server corrupted. Luckily there was a backup. If not.. somebody should start updating their CV's... :)

So, one of the affected servers was our Primavera server. So we had to restore it back from backup, the whole VM, and restore back the SQL database.

After restoring the Server and Database, you'll notice that the SQL services could not be started. So, what you should do is to install or remove your database instance from the Add/Remove Programs and reinstall/recreate the SQL instance.

When recreating the SQL instance, you can use back your previous instance to save you the trouble of going to each Primavera user to repointing the database. I've learned it "the hard way". So, after you read this, it will save you time if you have so many users.

Then, login using the SQL Management Studio and select your primavera database, create the pubuser and privuser, then reconnect the two user account to the database using a simple SQL script, which I will show you later.

Ask your user to test whether they can reconnect the P6.

Here are the steps from restoring your VM server from backup to connecting using P6.

1. Restore your Primavera server
2. Uninstall SQL Server from Primavera and remove the affected instance.
3. Install SQL Server. Name your SQL instance as the same as your previous one.
4. Open SQL Server Configuration Manager in your Primavera Server
5. Go to SQK Server Network Configuration, an select Protocols for your SQL instance (e.g; MSSQLSERVER)
6. Click IP Address tab, and scroll to the bottom under IPAll to TCP Port . Change the port to 1433. (which is normally the default settings for Primavera SQL database)
7. Restore SQL database from backup to the same folder
8. Open SQL Server Management Studios, connect using sa. Default password is Prima123Vera.
9. Check whether your database has been restored.
10. Select your database, expand security - users.
11. You'll notice that privuser and pubuser is missing.
12. Create privuser  and pubuser
13. Now, click New Query at the top.
14. Type these scripts and execute (you can copy and paste this scripts);

    EXEC sp_change_users_login 'Update_One', 'pubuser', 'pubuser';
    EXEC sp_change_users_login 'Update_One', 'privuser', 'privuser'

15. Do this to all your attached Primavera database.
16. Open your P6 application, and connect to the database.

Your Primavera should be working fine now.