|
SQL Server database recovery models give you backup-and-restore
flexibility. The model used will determine how much time and space
your backups will take and how great your risk of data loss will
be when a breakdown occurs.
System breakdowns happen all the time even to the best configured
systems. This is why you have to explore the options available in
order to prepare for the worst!
 SQL
server database recovery can be easier achieved if you are running
on at least the SQL server 2000. It has a built in feature known
as the database recovery model that controls the following
- Both the speed and size of your transaction log backups.
- The degree to which you might be at risk of losing committed
transactions in the event of media failure.
Models
There are three types of database recovery
models available
- Full Recovery
- Bulk_Logged Recovery
- Simple Recovery
Full Recovery
This is your best guarantee for full data recovery. The SQL Server
fully logs all operations, so every row inserted through a bulk
copy program (bcp) or BULK INSERT operation is written in its entirety
to the transaction log. When data files are lost because of media
failure the transaction log can be backed up.
- Database restoration up to any specified time can be
achieved after media failure for a database file has occurred.
If your log file is available after the failure, you can restore
up to the last transaction committed.
- Log Marks feature allows you to place reference points
in the transaction log that allow you to recover a log mark.
- Logs CREATE INDEX operations. Recovery from a transaction
log backup that includes index creations is done at a faster pace
because the index does not have to be rebuilt.
Bulk_Logged Recovery Model
This model allows for recovery in case of media failure and gives
you the best performance using the least log space for certain bulk
operations, including BULK INSERT, bcp, CREATE INDEX, WRITETEXT,
and UPDATETEXT.
Simple Recovery Model
It allows for the fastest bulk operations and the simplest backup-and-restore
strategy. Under this model, SQL Server truncates the transaction
log at regular intervals, removing committed transactions. Only
full database backups and differential backups are allowed.
|
