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.

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.

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s