Microsoft’s Project is a very standard project management tool that most Project Managers are all to familiar with. While Project is a great application it was limited to local information. Microsoft’s Project Server extends Project’s capabilities and allows the sharing of resources and information across an Enterprise.

Project Server 2007 is built around the SharePoint platform and creates four databases upon installation that house project related data. The four databases are Archive, Draft, Published, and Reporting as can be seen in the image.

For any DBA who has tried to deal with SharePoint restores you know the nightmare that can become. Thats a topic for another post entirely. After one such incident and a help ticket with Microsoft it came to light that Project Server has a built in ‘backup’ capability. Microsoft’s words not mine. It is really more akin to version control than backups but that is besides the point.

In the Schedule Backup section of the Server Settings a Project Retention Policy (versions) setting can be found. By default this is set to zero. Microsoft’s best practices are that this be set to a minimum of 3.

Now on to why DBAs supporting Project Server need to know what is going on here. Project server operates by storing data in three of the four Project Server databases. When a project is created and saved to the server but not ‘Publish’ it stores the information in the, you guessed it, Draft database. Once the project has been published it moves to the Published database. I know, you are still with me. When the retention policy is set to 3 the Archive database is used to retain this information. Again not shocking. What needs to be planned for is the space that this will take. Your archive database will be, in this example, 3 times larger than your published database as well as the server activity that will be occurring when the ‘backup’ is taken.

Above is a shot of what gets backed up and when it does. This can be found in below the Project Retention Policy box on the Schedule Backup screen. It was also Best Practices to stagger these times so they didn’t all hit the Project Server queue at once.

While I have enjoyed using Project Server as a Project Manager can sometimes be a pain in the butt as a DBA. Do you have any Project Server stories you would like to share?