Database Recovery Models
Every database require a recovery model which signifies that what sort of backup is required or can be perform by the user to restore the data which could be lost due to any hardware failure or other issue.
There are generally three types of recovery models of database, these are explained as following below.
1. Simple Recovery :
In this model, the transaction logs get automatically removed without causing and change to the files size, because of this it is difficult to make log backups. Simple Recovery does not support backup of transaction log. It supports both full and bulk_logged backup operations.
Some operations that aren’t supported by this model are : Log shipping, AlwaysOn or Mirroring and Point-in-time restore.
In this case the database is use only for testing and development. The data in this operation is static. It does not have the provision for point-to-time recovery.
2. Full Recovery :
Unlike simple recovery, it supports backups of transaction log. There will no loss of work due to damaged or lost files as this model keeps track of every operation performed on database.
It supports point-in-time for recovery for database, because of which it can recover upto an arbitrary point. When this model is use by database, the transaction logs will grow in huge number(infinitely) which will cause a problem like system crash. So to prevent it we must backup transaction log on regular basis.
This setup provides more options.
3. Bulk logged :
This model has similarity with Full Recovery Model as in both transaction logs are backup. It has high performance for bulk operations. It helps in importing bulk data quicker than other model and this keeps the transaction file size low. It did not support point-in-time recovery.
If you perform the transactions under this model which require transaction log restoration, then there could be data loss.