What are the recovery models and how does the log use them?

By: Brady Upton

I mentioned recovery models a few times in the sections above so I’ll give a brief overview of each one…

Full – In the Full recovery model, log files should be backed up on a regular basis to prevent the disk from filling up the drive. Also, in the Full recovery model, you can restore a database to the point of failure depending on your log backup schedules. This is the default for the model database in SQL Server. If you leave this recovery model set to Full, make sure you schedule log backups.

Simple – In the Simple recovery model, transaction log backups cannot be used. This means that SQL Server will automatically reclaim disk space at certain intervals, which can be good, but it also means that if a failure were to occur you can only restore back to the last full backup since all transactions are lost in between. This is generally used in development environments.

Bulk Logged - In the Bulk Logged recovery model, certain large scale or bulk copy operations are minimally logged. I have never left a database in the Bulk Logged recovery model for a large amount of time. I usually will have a database in the Full recovery model and if I’m doing a bulk insert and don’t need to log the transactions, I’ll switch to Bulk Logged and then back to Full once this is complete. Bulk Logged, however, does support log backup and point in time recovery.

Set Recovery Model in SQL Server Management Studio

To change the recovery mode in SSMS, right click the database, choose Properties. On the Database Properties window, select Options, Recovery Model:

Set Recovery Model using TSQL

        Use [master]
        GO
        ALTER DATABASE [AdventureWorks2012] SET RECOVERY SIMPLE WITH NO_WAIT
        GO
        

Replace AdventureWorks2012 with your database name and use SIMPLE, FULL, or BULK_LOGGED for the recovery model.