SQL Server
provides three recovery models to determine how transactions are logged and the
level of exposure to data loss. They are Simple Recovery, Full Recovery, and
Bulk-Logged Recovery.
Simple
Recovery Model
With the
Simple Recovery model, the database can be recovered to the point of the last
backup. However, you cannot restore the database to the point of failure or to
a specific point in time. For PeopleSoft production environments, it is not
recommended to use the Simple Recovery model. You can consider using the Simple
Recovery model for your development environment. This prevents extensive growth
of log files and is easy to maintain.
Full
Recovery Model
The Full
Recovery model uses database backups and transaction log backups to provide
complete protection against media failure. If one or more data files is
damaged, media recovery can restore all committed transactions. In-process transactions
are rolled back. Full Recovery provides the ability to recover the database to
the point of failure or to a specific point in time. To guarantee this degree
of recoverability, all operations, including bulk operations such as SELECT
INTO, CREATE INDEX, and bulk loading data, are fully logged. Use the Full
Recovery model for your production environment to enable point-in-time
recovery.
In Full
Recovery model, log backups are required. This model fully logs all
transactions and retains the transaction log records until after they are
backed up. The Full Recovery model allows a database to be recovered to the
point of failure, assuming that the tail of the log can be backed up after the
failure. The Full Recovery model also supports restoring individual data pages.
Bulk-Logged
Recovery Model
The
Bulk-Logged Recovery model provides protection against media failure combined
with the best performance and minimal log space usage for certain large-scale
or bulk copy operations. Operations such as SELECT INTO, CREATE INDEX, and bulk
loading data are minimally logged, so the chance of a data loss for these
operations is greater than in the Full Recovery model.
In
addition, the Bulk-Logged Recovery model allows the database to be recovered
only to the end of a transaction log backup when the log backup contains bulk
changes. Point-in-time recovery is not supported.
In this
model, log backups are still required. Like the Full Recovery model, the
Bulk-Logged Recovery model retains transaction log records until after they are
backed up. The tradeoffs are bigger log backups and increased work-loss
exposure because the Bulk-Logged Recovery model does not support point-in-time
recovery.
Note: For a PeopleSoft production
environment, to minimize work and data loss due to lost or damaged data files,
it is always recommended to use the Full Recovery model. However, make sure
that transaction log backups are scheduled frequently.
