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. 

4. Differential Backup:
In addition to the backup types supported by the full and bulk-logged recovery models, the full recovery model also supports differential backups. A differential backup only backs up the data that has changed since the last full backup, which can save time and space compared to doing another full backup.

5. Log Sequence Number (LSN):
LSN is a unique identifier that is used to track every modification made to the database. It is used to ensure that all changes are recorded and to keep track of which backups need to be restored in case of a disaster.

6. Point-in-time Recovery:
This feature allows the user to restore the database to a specific point in time, using the transaction log backups. It is only supported in the Full Recovery Model and requires regular backups of the transaction log.

7. Increased Complexity:
As the recovery models become more advanced, the complexity of managing and maintaining the database also increases. This can require more expertise and resources from the database administrator.

8. Increased Storage Requirements:
Full and bulk-logged recovery models require regular backups of the transaction logs, which can quickly add up to a significant amount of storage space. This can also increase the cost of maintaining the database.

If you perform the transactions under this model which require transaction log restoration, then there could be data loss.