Recovery Models in SQL Server

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 Recovery
• Bulk Logged Recovery
• Simple Recovery

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.