Recovery Models - SQL Server

Understanding the recovery models of the databases in SQL server is important to implement the effective backup and disaster recovery strategy. Recovery models will tell you how long and what data need to be stored in Log file. Three types of Recovery models in SQL server are FULL, Bulk logged and simple. A database can have only one recovery model in use and can be changed by T-SQL or from SQL Server Management studio. Taking a full backup is suggested after changing the recovery model of the database.

FULL Recovery:  By selecting Full recovery model all transaction data is recorded in transaction log until a log backup or truncate happens.  All the changes are logged in to log files before effecting the data in primary file, so by selecting the full recovery model the database can be recovered to certain point in time in case of failure.

Syntax: ALTER DATABASE <dbname> SET RECOVERY <recoveryoption>

Ex:  Alter database student set recovery FULL

Using SQL Server Management Studio (SSMS)

1)     Right click the Database and select properties

2)     Click on Options

3)     Select FULL recovery model dropdown.

4)     Click Ok

Bulk Logged: This is a special type of recovery and can be used to improve the performance when heavy bulk operations happen on database by reducing the data being logged. This Recovery model does not log the bulk operations but you can recover the database to point in time provided they are no bulk operations during that period on the database. Some of the bulk operations are create Index, bcp, bulk insert etc..

Syntax: ALTER DATABASE <dbname> SET RECOVERY <recoveryoption>

Ex: Alter database student set recovery BULK_LOGGED

Using SQL Server Management Studio (SSMS)

1)     Right click the Database and select properties

2)     Click on Options

3)     Select Bulk-Logged recovery model dropdown.

4)     Click Ok

Simple: It’s the basic recovery model. This model is applied to testing and non critical databases.  Log files does not grow as much as FULL and Differential backup’s and also

log files are reused in this recovery model hence it cannot be recovered point in time. Full and differential backup’s can be configured but not the Log backups.

Syntax: ALTER DATABASE <dbname> SET RECOVERY <recoveryoption>

Ex: Alter database student set recovery SIMPLE

Using SQL Server Management Studio (SSMS)

1)     Right click the Database and select properties

2)  Click on Options

3)   Select Bulk-Logged under recovery model dropdown.

4)   Click Ok