Understanding SQL Server Recovery Model
A Recovery Model is a database property that designed to control transaction log maintenance. SQL Server Recovery Model does not define how transactions will be logged but they define how transaction logs will be managed in the transaction log file. There are three types of recovery models in SQL Server:
Simple Recovery Model
Simple recovery model is used for databases that are not critical or the database is static and data is not changing. We generally use SIMPLE model for lower life cycle databases. Main reason for this is point in time recovery is not possible because transaction log backup is not allowed in this recovery model and database engine automatically clears transactional logs during checkpoint operation. If you want to understand checkpoint operation, you should read attached article.
A common myth is that when a database is configured in Simple recovery model then transactions will not be logged in transaction log files. This is not true and every transaction is logged to the transaction log file. Only difference is that, when that transactions will complete and data is written to disk all logs will flush and be available to log another transaction in same log file. This way you can also deal with the space issue.
Log Shipping, AlwaysOn Availability group and Point-in-Time Restores are not possible in this SIMPLE model because transaction log backups are not allowed in this recovery model. We can recover any database that are running in SIMPLE model till last full or differential backups only. Run below command if you want to view or change the recovery model of the database.
--Check the Recovery Model of the database YourDB_Name SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'YOURDB_NAME' ; GO --Change Recovery Model to SIMPLE ALTER DATABASE DBNAME SET RECOVERY SIMPLE GO
Full Recovery Model
Point in time recovery is possible in full recovery model that is why we use this recovery model for all production and critical databases. Every transactions that are logged in this recovery model can be backed up. Transaction log backup will capture all the logs and clears all the inactive portions of the transaction log file. You can handle your log file space management by scheduling frequent transaction log backups.
You can also clear the transaction logs from log files by switching the recovery model from Full to Simple but this is never recommended for any production database. If you change the SQL Server recovery model from Full to Simple, logs will be flushed from log file without getting captured into transaction log backup. That will break the log sequence and you will end up losing the point in time recovery for your database. That is why switching recovery model from full to simple is not recommended for production database.
As transaction log backups are allowed in this recovery model so you can recover your database till point in time and there will be no data loss in this recovery model.
--Change SQL Server Recovery Model to FULL ALTER DATABASE DBNAME SET RECOVERY FULL GO
Bulk-Logged Recovery Model
Bulk-logged recovery model works similar to Full recovery model except few exceptions during bulk operations. You can use this recovery model if you have to run BULK operations on your database. Bulk-logged recovery model minimally logged such bulk operations to reduce the transaction log space.
Point in time recovery is not possible if you have performed any bulk operation during that time due to the nature of minimally logged operation. If you haven’t performed any bulk operation then point in time recovery can be achieved. Microsoft does not suggest to run any database in bulk-logged recovery model if you don’t have to run any bulk operations.
Best practice is that you change a database recovery model to the bulk-logged immediately before running bulk operations and restore it back to the full recovery model when those operations complete. Also you should run a log backup before switching to bulk-logged recovery model and again take the log backup once you return back to full recovery model post completing bulk operation. Your backup strategy remains the same and it will not break your log sequence. Run below command to change the recovery model to bulk-logged.
--Change Recovery Model to BULK-Logged ALTER DATABASE DBNAME SET RECOVERY FULL GO
Change Recovery Model using GUI
- Connect to SQL Server Instance. in Object Explorer, click the server name to expand the server tree.
- Expand Databases folder and click on the database for which either you want to change the recovery model of view the recovery model.
- Right-click on that database, and then click Properties, which opens the Database Properties dialog box.
- In the Select a page pane, click Options.
- The current recovery model is displayed in the Recovery model list box.
- Optionally, to change the recovery model select a different model list. The choices are Full, Bulk-logged, or Simple.
- Click OK.
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018