SQL Server recovery models

Each database hosted by SQL Server contains a property called a recovery model. This property basically affects which backup strategy can be designed. This short section briefly explains recovery models.

How to configure a database's recovery model property

Every database has a property called the recovery model. The recovery model determines how transactions are logged, and for what timespan the transactions will be stored in the transaction log. The recovery model is set by the ALTER DATABASE TSQL command:

-- setting full recovery model ALTER DATABASE <database_name> SET RECOVERY FULL

The recovery model has three possible options:

• SIMPLE
• BULK_LOGGED
• FULL

We'll take a look at these now.

Using the SIMPLE recovery model

When the recovery model is set to SIMPLE, SQL Server clears transactions from the transaction log at every checkpoint. This approach leads to a...