Does anyone have any pros and cons about the Recovery Model settings for the
ProjectServer_xxxx databases? I noticed very large .LDF files relative to
the .MDF files during a data move and just wondered if anyone can shed any
light on Full vs. Simple Recovery Mode relative to Project Server/SharePoint
Services functionality.
We've got a fairly small Project Server environment running an
all-in-one-box configuration.
The issues here aren't particular to Project Server.
Full Recovery allows you to restore to any particular point in time
previous to a Log backup.
Simple allows you to restore only to the point in time that the
database was backed up.
So if someone really trashed the database at 11:12 AM, with Full
Recovery you can restore to the state of things at 11:11 AM, but with
Simple you can only restore to whenever the last backup was made.
The cost of Full Recovery is that the log files need to be backed up
and kept to enable the point-in-time restore. Also, the active .ldf
log file can grow between log backups.
To our clients, I generally recommend that the Project Server
databases follow whatever the corporate standard is for non-business
critical databases. This often means they are set to Simple recovery
model.
If someone screws up their project, reverting to the previous days
version can be done easily with Project Server's archive, and if the
database blows up, I don't think losing less than a days worth of time
updates is usually a catastrophic loss. I think more is gained by
following whatever procedure you and your organization are comfortable
with, so that backups and restores fit into your organizations
existing practices.
One big exception to this: If performing a migration or other one-time
large transaction volumes, set the databases to Simple for the
duration of this. It will speed up SQL performance slightly and you
don't need the logs of that activity.
Also, you should treat the four projectserver database backups as a
set. If you restore one, restore all of them. Take backups within a
few minutes of each other. Mixing the databases up (like restoring
only one of the DBs) does lead to problems.
Hope this helps...
James Fraser
