Recovery Models in SQL Server

Database Recovery models plays an important role in the data recovery and high availability possibilities in SQL Server. Complete behaviour of Transaction Log file of a database depends on recovery models. The following features of Transaction Log depends on recovery models of database.

            1. What is recorded in Transaction Log File.
            2. Which types of backups are possible.
            3. When the Transaction Log file is truncated.
            4. Log shipping , Database mirroring are possible or not.
            5. Point in time recovery is possible or not.

• SQL Server supports 3 types of recovery models
• Full
• Bulk Logged
• Simple

We can set the recovery model of database as follows
    USE MASTER
    GO
   ALTER DATABASE <dbName> SET RECOVERY <FULL/BULK_LOGGED/SIMPLE>

We can check the recovery model of database from sysdatabases or sys.databases view of master database.