Plan for disaster recovery in Project Server 2010
Applies to: Project Server 2010
Topic Last Modified: 2011-08-05
There are two methods of backing up your Microsoft Project Server 2010 data:
Using Microsoft SharePoint Server 2010 tools
Using Microsoft SQL Server tools
Important
Choose the option that best fits the needs of your organization. We do not recommend combining the two options.
Backing up by using SharePoint Server 2010 tools
The backup and restore tools that are provided as part of SharePoint Server 2010 can perform basic backup and restore functionality for Project Server 2010 databases.
The SharePoint Server 2010 backup and recovery tools have the following advantages:
The SharePoint Central Administration Web site provides a simple user interface from which backup and recovery operations can be performed.
Windows PowerShell can be used together with the Windows Task Scheduler to easily schedule backup operations during times when the system is not being used.
The SharePoint Server 2010 backup and recovery tools have the following limitations:
They cannot synchronize the Project Server 2010 databases. Because of this, backups should be done when the system is not being used, to avoid cross-database inconsistencies.
They do not use SQL Server transaction-log-level backups. In the event of database loss, there is no allowance for recovery to the point of failure.
Backing up by using SQL Server tools
SQL Server backup and recovery tools allow for the greatest versatility in recovering your Project Server 2010 deployment after a failure. SQL Server tools have the following advantages:
Using the Full Recovery model for SQL Server databases allows for recovery to the point of failure in the event of a database loss.
Project Server 2010 and related databases can be synchronized by using transaction log marking. This allows for database consistency during database recovery.
SQL Server tools have the following disadvantage:
- SQL Server procedures can be complex and require a good knowledge of SQL Server itself and SQL Server databases. We recommend a trained SQL Server database administrator be responsible for these procedures.
Which should I use?
Which backup method to use depends on the requirements of your organization and the availability of trained resources to manage the process. SQL Server tools offer the most versatile options for recovery, but they are more complex to use. SharePoint Server 2010 tools are easy to use, but only allow recovery to the last full or differential backup, which means that there will generally be some data loss. SharePoint Server 2010 tools also require the system to be backed up during a period of inactivity to ensure data consistency across databases.
Use SharePoint Server 2010 tools if:
You have a deployment that is not being used for several hours, usually at night, when backups can be performed.
You can tolerate data loss of up to twenty-four hours (the time since the last daily backup).
Your organization does not have trained SQL Server database administrators or IT personnel who can manage the processes of using SQL Server tools.
Note
If you use SharePoint Server 2010 tools, make sure that you back up all Project Server 2010 databases and the associated content database.
Use SQL Server tools if:
You have a large enterprise deployment that may be in use at any time of the day or night.
You have a data loss tolerance (Recovery Point Objective) of less than twenty-four hours.
You want to be able to recover to the point of failure in the event of a hardware failure such as the loss of a disk.
The remainder of this section covers the use of SQL Server tools for backing up and restoring Project Server 2010 databases. Detailed instructions for using SharePoint Server 2010 backup and recovery tools to back up Project Server 2010 databases are covered in Back up a Project Server 2010 farm by using built-in tools. For information about how to back up the rest of your SharePoint Server 2010 farm, see Back up a farm in SharePoint Server 2010.
Enterprise disaster recovery using SQL Server tools
Project Server 2010 data and SharePoint Server 2010 data is stored in SQL Server databases. To protect your data against loss, you can create database backups by using the tools that are provided with SQL Server. Database backups contain a complete record of all the data in your database at the time that the backup finishes. Database backups can be used in combination with transaction-log backups to restore your Project Server 2010 and SharePoint Server 2010 data to the point of failure or to an earlier point in time.
Because Project Server 2010 data is stored in multiple related databases, we recommend that you use marked transactions in the transaction logs of each database to help synchronize the databases during recovery.
Setting the recovery model
Set the recovery model for each database in your deployment as follows:
Project Server 2010 databases: Full Recovery Both point-of-failure recovery and recovery to earlier points in time require full backups and transaction-log backups of your Project Server 2010 databases.
Note
We recommend that you move your Project Server 2010 databases to fault-tolerant storage.
Project Server 2010 content database(s): Full Recovery These databases contain the project workspaces for your Project Server 2010 deployment. Both point-of-failure recovery and recovery to earlier points in time require full backups and transaction log backups of the SharePoint Server 2010 content databases associated with your Project Server 2010 deployment.
Note
We recommend that you move your content databases to fault-tolerant storage.
Msdb: Simple Recovery This database contains the backup and recovery history and also the log marking history for all the databases. You need this information when you are recovering your Project Server 2010 deployment. We recommend a daily backup of the Msdb database.
Note
We recommend that you move the Msdb database to fault-tolerant storage.
Master: Simple Recovery This database contains SQL Server security and configuration information. We recommend a daily backup of the Master database.
If you are backing up a non-critical Project Server 2010 deployment, you can set all databases to Simple recovery if you are able to tolerate the loss of all data between backups.
Important
If you use Simple recovery, you should back up your deployment when the system is not being used (that is, when no database transactions are occurring). This ensures database consistency after recovery.
For more information about SQL Server recovery models, see Backing Up and Restoring Databases in SQL Server (https://go.microsoft.com/fwlink/p/?LinkID=102629) in SQL Server Books Online.
Database synchronization
SQL Server backup functionality backs up individual databases independently of one another. Because of this, if you have multiple databases that interact with one another and you use standard SQL Server full or differential backups, related databases can be left in an inconsistent state when restoring.
The backup features in both the SharePoint Central Administration Web site and Windows PowerShell command-line tools start standard SQL Server full or differential backups. Therefore, these tools can also leave related databases in an inconsistent state when restoring.
Project Server 2010 has four dedicated databases for each instance of Microsoft Project Web App. To avoid data inconsistencies and minimize the possibility of orphaned records, these databases should be synchronized during recovery operations. There are two primary methods of achieving this:
Use the full recovery model for each database and restore each database to the point of failure
Use the full recovery model for each database and restore each database to a marked transaction
If the active transaction log is still available after a hardware failure, then recovery to the point of failure is the preferred method. This ensures minimal or no data loss and is the best option for recovering the databases to a consistent state.
If the active transaction log is damaged or unavailable for one or more related databases, we recommend that you recover to a named mark in the transaction log. This synchronizes the databases from a SQL transaction perspective and minimizes the risk of related databases being recovered to an inconsistent state.
Logical transactions
Both Project Server 2010 and SharePoint Server 2010 use logical transactions that require multiple SQL transactions in order to complete. Creating a site in SharePoint Server, for example, initiates several SQL Server stored procedures and requires multiple transactions in SQL Server to accomplish. Such logical transactions include but are not limited to:
Creating a Web application or site collection
Provisioning a Project Web App site
Logical transactions are usually the result of manually initiated administrative functions as opposed to regular functions done by users such as working with documents or lists, or entering time on a project.
If a database is restored to a point in time in the middle of a logical transaction, the Project Server 2010 databases may have orphaned records and the application may not function correctly.
When restoring one or more databases, we recommend not restoring to a point in time where a logical transaction was taking place.
Marking the transaction logs
A marked transaction is a transaction that is given a name, such as a time/date stamp. By applying marked transactions to all the databases in your deployment simultaneously, you create a consistent point in all databases to which you can recover.
Related database recovery does not allow recovery to an arbitrary point in time. Recovery of related databases to any time earlier than the point of failure can only be accomplished by recovering to a marked transaction or to a point in time when the system was not in use.
To mark transactions across related databases, place distributed marks across all databases before backing up the log in any database. This ensures that all log backups have a mark that appears in all databases and allows synchronization during recovery.
To recover related databases to a point in time prior to the point of failure, you must have the following:
A full database backup of each database
Subsequent transaction log backups of each database containing a distributed named mark
Important
The transaction-log backup for each database must contain identical distributed named marks.
Because recovering to a named mark requires restoring at least one transaction-log backup, we recommend that you use the following procedure when backing up databases that use log marking:
Back up each database.
Mark the transaction logs.
Back up the transaction logs.
Store the database and transaction log backups together.
For more information about transaction log marking, see Using Marked Transactions (Full Recovery Model) (https://go.microsoft.com/fwlink/p/?LinkID=214293) in SQL Server Books Online.
Recovering databases
When recovering from a failure or disaster, it is necessary to recover related databases to a point of consistency with each other to maintain data integrity and avoid orphaned records and possible system problems. Specifically, the following databases must be synchronized with each other:
Project Server 2010 Draft database
Project Server 2010 Published database
Project Server 2010 Reporting database
SharePoint Server 2010 Content database associated with the given Project Web App instance
The Project Server 2010 Archive database is only accessed when archiving data by using the Project Web App Administrative Backup functionality. Because of this, the Archive database does not require synchronization with other Project Server 2010 databases and can be recovered to any point in time.
Note
As a best practice, do not recover the Archive database to a point in time when an Administrative Backup was in process.
Normally, if the active transaction log is available on each database that you are restoring, you can recover to the point of failure and the databases will be consistent. If the active transaction log is not available for one or more databases that you are recovering, then you must recover to an earlier point in time. In this case, you should recover to a named mark in the transaction log to help assure data integrity.
The following table provides recovery guidance for the different types of databases associated with Project Server 2010 deployments.
| Database | Recovery procedure |
|---|---|
Project Server 2010 Archive database |
Recover to the point of failure, if possible. If you are recovering to an earlier point in time, recover to a point in time when no administrative backup operation was in progress. |
Project Server 2010 Draft database |
Recover to the point of failure, if possible. If you are recovering to an earlier point in time, synchronize with the Project Server 2010 Published and Reporting databases and the SharePoint Server 2010 Content database associated with the Project Web App instance. |
Project Server 2010 Published database |
Recover to the point of failure, if possible. If you are recovering to an earlier point in time, synchronize with the Project Server 2010 Draft and Reporting databases and the SharePoint Server 2010 database associated with the Project Web App instance. |
Project Server 2010 Reporting database |
Recover to the point of failure, if possible. If you are recovering to an earlier point in time, synchronize with the Project Server 2010 Published and Draft databases and the SharePoint Server 2010 Content database associated with the Project Web App instance. |
SharePoint Server 2010 content databases |
Recover to the point of failure, if possible. Synchronize with Project Server 2010 databases if recovering to an earlier point in time. |
When recovering your databases, keep in mind the following best practices:
If a SharePoint Server 2010 content database failed during a major business transaction (such as creating a site or a Web application), recover that database to a point in time before the business transaction began.
If one or more Project Server 2010 databases failed, recover them to the point of failure, if possible. If point-of-failure recovery is not possible, then recover the Published, Draft, and Reporting databases and the SharePoint Server 2010 content database associated with your Project Server 2010 deployment to a marked transaction.
After restoring your databases, we recommend that you review current unprocessed and recent processed queue jobs to check for anomalies.