What is recovery model? What are those ?

Recovery Models

SQL server has three types of recovery models.

1. Full

2. Bulk Logged

3. Simple

Full

·         In full recovery model all the database operations are fully logged like all redo information. This means all the modifications have written fully to the log file.

·         The full recovery model is always the best one for production servers.

·         In the full recovery model we can take all kinds of backups like full, differential, transaction Log and point in time recovery (PTR) too.

Full recovery mode, the transaction log file will grow like anything(big), until we do a log backup. In other words, The log file will not be reusable/clear until the log backup taken.

Bulk Logged

·         The name itself you can understand the answer. The bulk operations (bulk operations) are minimally logged in this mode. This means it does not have sufficient information in the log to replay the transaction. The BCM page contains all the required information. See an example you can understand.

·         Bulk logged recovery model is the best model for server performance. Because all the bulk changes have been written minimally (Not fully) to the transaction log file.

·         In bulk logged recovery model we can take all kinds of backups like full, differential and transaction Log but, the drawback is the point in time recovery (PTR) is not possible, when there is a bulk operations  have done with the transaction log file.

Keep in mind, in full and bulk logged recovery model the log files grow bigger until the BACKUP LOG has done.

For more clarity. See an example: (This is not a concept just an example)

Just assume 5,000 bulk operations have written in 8000 pages.

In full recovery model each 5,000 bulk operations have written in 8000 pages, because all the changes have written fully in the full recovery model.

But when we used bulk logged recovery model, it has written 1000 pages only ,Because SQL server has written the bulk changes to an extent wise not the page wise i.e. minimally logged operation (8 pages = 1 extent). It will help to improve the overall server performance.

Now you know, Writing 8000 pages are good or 1000 pages.

Note: The bulk logged model is good only for the bulk operations.

Simple

·         The name itself you can understand the answer all operations are fully logged except bulk operation, Since bulk are minimally logged. Simple recovery model is just simple this means SQL server will run the checkpoint every time and truncate the transaction log file and marked the space for reuse . Mostly the log file will not grow larger.

·         Most of the time the simple recovery model is a good choice for non production servers. Because the log file will not grow larger. Also we would not take log backups. (If you’re planning to take the log backup then don’t put in simple)

·         In the simple recovery model the transaction log backup is not possible. We can take full and differential backups only.