SQL SERVER – Select Best Recovery Model for your database

Recovery model for sql server database is depends on customer requirement, SQL Server supports three recovery models, Full recovery, Simple recovery and bulk-logged recovery. Recovery models tells you whether How much data loss is acceptable by customer.

SIMPLE RECOVERY

If your database is not critical and Data loss is accepable by client after last full or differential backup than you can select SIMPLE RECOVERY Model.
Here if client data does not change frequently and easy to recover incase of data loss than SIMPLE Recovery model is preferable.

How to set SQL Server Database in SIMPLE Recovery Model ?

Syntax :

–Set Recovery Model to Simple
ALTER DATABASE SQLDEMO SET RECOVERY SIMPLE

BULK-LOGGED RECOVERY

If your database is critical but bulk operation or large data transaction need to be minimal log (Only allocation pages) to avoid performance impact, You can select BULK_LOGGED Recovery Model.
Here, Point in time recovery in not possible in BULK_LOGGED recovery model.

Note : Switch to FULL recovery from BULK_LOGGED after BULK Operations (like Rebuild index, bulk copy, select into statement) completes and take log backup for point in time recovery.

How to set SQL Server Database in BULK_LOGGED Recovery Model ?

Syntax :

–Set Recovery Model to FULL

ALTER DATABASE SQLDEMO SET RECOVERY BULK_LOGGED

Steps to recover database incase failure

* Take tail log backup of database

* Restore the most recent full backup

* Restore the most recent differential backup if any

* Apply all transaction log in sequence since last differential or Full backup

* Now manually update the changes since the last log backup

NOTE :Point in Time recovery does not work in BULK_LOGGED recovery model.

FULL RECOVERY

Full recovery model is fully loged operation, No data lose. You can able to recover database up to point in time.
For point in time recovery, you need to stay in FULL Recovery model. There is no problem in switching from FULL to BULK LOGGED recovery model to perform bulk logged operations (such as rebuild indexes, bulk copy etc), Just ensure once operation completed, you switch to FULL and take log backup.

How to set SQL Server Database in FULL Recovery Model ?

Syntax :

— Set recovery model of your database to FULL

ALTER DATABASE SQLDEMO SET RECOVERY FULL

 

Leave a Reply

  

  

  


7 − = 3