How many types of Recovery Models?
Difference B/w them and Advantages and Disadvantages.
We have three
types of recovery models SIMPLE, FULL, BULK-LOGGED.
In SIMPLE recovery model database log file
will have only active/uncommitted transactions, all inactive/committed
transactions will be truncated automatically and you can’t take log backup in
simple recovery model because SQL Server will automatically truncate all the
committed transactions.
In FULL recovery
model database log file will have both active/uncommitted and
inactive/committed transactions.
You can take the
T-Log backup in FULL recovery model and you can do point-in-time recovery.
If you configure
the FULL recovery model for your database you should configure the regular
interval T-Log backups to maintain the log file size and for recovery purpose
in case of database crash.
BULK-LOGGED, log
file will have both committed and uncommitted transactions and you can take
T-Log backup also but you can’t do point-in-time restore.
SQL Server will
log bulk logged operations minimally i.e. BULK INSERT, BCP, SELECT INTO when you
perform these operations SQL Server will log minimally, minimally means it will
log only the information required to rollback the transactions and avoid
transaction log file growth.
This recovery
model introduced in SQL Server 2000 to control the log files growth while
performing bulk operations.
please go through the fallowing links:
http://blog.sqlauthority.com/2007/06/13/sql-server-recovery-models-and-selection/
No comments:
Post a Comment