DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 323330 - Last Review: July 3, 2013 - Revision: 6.0

This article was previously published under Q323330

On This Page

Summary

This article explains how you can create, schedule, and restore a backup of your Microsoft Project Server database.

Back Up the Database

After you have completed the Microsoft Project Server installation, Microsoft recommends that you create a backup of the new database/s. The version of Project Server you are using determines the number of database that require backup. All versions of Project Server will have at least one content database associated with the Project site collection where Project Web App is provisioned. 

Project Server 2002, 2003 and 2013 - one project server database

Project Server 2007 and 2010 - four project server databases with names ending in Archive, Draft, Published and Reporting.



Follow the steps below for your version of SQL Server.

To create a backup by using Microsoft SQL Server 2005 or greater, follow these steps: 
  1. Click Start, click AllPrograms, click Microsoft SQL Server, and then click SQL Server Management Studio.
  2. In the Connect to Server dialog, the Server Type should be "Database Engine". Check the Server name and Authentication and make changes if needed, then click the Connect button.
  3. Expand SQL Server if needed and then expand Databases.
  4. Right-click the database to be backed up, point to Tasks, and then click Back Up….
  5. The General page will display; under the Destination heading, click Add next to the Backup to box.
  6. In the Select Backup Destination box a path and filename are already populated.  You can keep this option or change it by clicking the ellipsis button (...) to browse the file system. Here you can select a new location and enter a new filename as well, then click OK twice.
  7. Click Options, click to select the Verify backup when finished check box, and then click OK to start the backup.
  8. Click OK in the backup confirmation dialog box.
Scheduling Backups

After you have completed the Microsoft Project Server backup, Microsoft recommends that you schedule regular backups of the database. The SQL Server Agent (MSSQLSERVER) service needs to be started for jobs to be scheduled and run. 

Start the Agent

To start the SQLSERVERAGENT service, follow these steps:
  1. Click Start, point to Administrative Tools, and then click Services.
  2. In the right pane, locate and right-click SQL Server Agent (MSSQLSERVER), and then click Start.
  3. Close the Services tool.
Schedule the Backup

To schedule backups in SQL Server 2005 or greater, use the Maintenance Plan Wizard.  To access the wizard follow these steps:
  1. Click Start, click AllPrograms, click Microsoft SQL Server, and then click SQL Server Management Studio.
  2. In the Connect to Server dialog, the Server Type should be "Database Engine". Check the Server name and Authentication and make changes if needed, then click the Connect button.
  3. Expand SQL Server if needed and then expand Management.
  4. Right-click Maintenance Plans and then click Maintenance Plan Wizard.
  5. The wizard builds the maintenance plans that will run per your instructions set via the wizard. Click Close.
Restoring the Database


To restore the Project Server database, you can use the restore feature of Microsoft SQL Server. To do this, follow these steps:
  1. Click Start, click AllPrograms, click Microsoft SQL Server, and then click SQL Server Management Studio.
  2. In the Connect to Server dialog, the Server Type should be "Database Engine". Check the Server name and Authentication and make changes if needed, then click the Connect button.
  3. Expand SQL Server if needed and then expand Databases.
  4. Right-click the database, point to All Tasks, and then click Restore then click Database….
  5. From the list of backups, click the backup to restore, and then click OK to start the restore.
  6. Click OK to confirm that the restore completed successfully.


To create a backup by using Microsoft SQL Server 2000 Enterprise Manager, follow these steps:
  1. Click Start, point to Programs point to Microsoft SQL Server, and then click Enterprise Manager.
  2. Expand Microsoft SQL Servers, expand SQL Server Group, expand your Microsoft Project Server database server, and then expand Databases.
  3. Right-click the Microsoft Project Server database, point to All Tasks, and then click Backup Database.
  4. Click the General tab; under the Destination heading, click Add next to the Backup to box.
  5. In the Select Backup Destination box, click File name, and then click the ellipsis button (...) to browse the file system.
  6. In the Backup Device Location dialog box, navigate to the file system location for your backup.
  7. In the File name box, type a name for the backup, and then click OK two times.
  8. Click the Options tab, click to select the Verify backup upon completion check box, and then click OK to start the backup.
  9. Click OK in the backup confirmation dialog box.

Scheduling Backups

After you have completed the Microsoft Project Server backup, Microsoft recommends that you schedule regular backups of the database.

Start the Agent

To run scheduled backups, start the SQLSERVERAGENT service, and then set it to an Automatic startup type. To start the SQLSERVERAGENT service, follow these steps:
  1. Click Start, point to Programs, point to Administrative Tools, and then click Services.
  2. In the right pane, locate and right-click SQLSERVERAGENT, and then click Properties.
  3. In the SQLSERVERAGENT Properties dialog box, click Automatic in the Startup type list, and then click Start.
  4. Click OK.
  5. Close the Services tool.

Schedule the Backup

To schedule backups, follow these steps:
  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
  2. Expand Microsoft SQL Servers, expand SQL Server Group, expand your Microsoft Project Server database server, and then expand Databases.
  3. Right-click the database used by Microsoft Project Server, point to All Tasks, and then click Backup Database.
  4. Click the General tab; under the Destination heading, click Add next to the Backup to box.
  5. In the Select Backup Destination box, click File name, and then click the ellipsis button (...) to browse the file system.
  6. In the Backup Device Location dialog box, navigate to the file system location for your backup.
  7. In the File name box, type a name for the backup, and then click OK two times.
  8. In the SQL Server Backup dialog box, click to select the Schedule check box.
  9. Click the ellipsis button (...) to edit the backup schedule.
  10. In the Edit Schedule dialog box, type a name for the schedule, modify the backup frequency, and then click OK.
  11. Click the Options tab, click to select the Verify backup upon completion box, and then click OK to start the backup.

Restoring the Database


To restore the Project Server database, you can use the restore feature of Microsoft SQL Server. After you restore the database, you must delete and then re-create the MSProjectServerUser and MSProjectUser accounts to make sure that the correct passwords are configured. To do this, follow these steps:
  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
  2. Expand Microsoft SQL Servers, expand SQL Server Group, expand your Microsoft Project Server database server, and then expand Databases.
  3. Right-click the Microsoft Project Server database, point to All Tasks, and then click Restore Database.
  4. From the list of backups, click the backup to restore, and then click OK to start the restore.
  5. Click OK to confirm that the restore completed successfully.
To delete and re-create the database users, follow these steps:
  1. Expand your restored Project Server database.
  2. Click Users, and then click the MSProjectServerUser user.
  3. Press DELETE, and then click Yes to confirm the deletion.
  4. Click MSProjectUser, and then press DELETE.
  5. Click Yes to confirm the deletion.
  6. Right-click Users, and then click New Database User.
  7. Under Login name, click MSProjectServerUser, and then under Database role membership select MSProjectServerRole.
  8. Click OK.
  9. Right-click Users, and then click New Database User.
  10. Under Login name, click MSProjectUser, and then under Database role membership select MSProjectRole.
  11. Click OK.
  12. Log on to Microsoft Project Web Access to verify the connection.

More information

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
241397  (http://support.microsoft.com/kb/241397/ ) How To Back Up a Microsoft Data Engine Database with Transact-SQL
 For additional information about database restoration, see the Disaster Recovery Guide at the following Micrososft Web site:

Project Server 2013 - http://technet.microsoft.com/en-us/library/jj993260.aspx (http://technet.microsoft.com/en-us/library/jj993260.aspx)

Project Server 2010 - http://technet.microsoft.com/en-us/library/dd207301(v=office.14).aspx (http://technet.microsoft.com/en-us/library/dd207301(v=office.14).aspx)

Project Server 2007 - http://technet.microsoft.com/en-us/library/dd207301(v=Office.12).aspx (http://technet.microsoft.com/en-us/library/dd207301(v=Office.12).aspx)



Applies to
  • Microsoft Project Server 2013
  • Microsoft Project Server 2010
  • Microsoft Office Project Server 2007
  • Microsoft Office Project Server 2003
  • Microsoft Project Server 2002
Keywords: 
kbhowto kbinfo KB323330
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support