Database Recovery Models in SQL Server
Switching Database Recovery Models
Recovery Model of a database can be switched from one recovery model to another any time depending up on the business requirements. You can switch the recovery model from Full to Bulk Logged for mission critical database during the maintenance time window. During the maintenance time window you can perform the bulk load operation and even go for index maintenance which normally results in excess growth of transaction log file when the database is in Full recovery model. Switching to Bulk Logged will result in minimal log of transactions thereby reducing the transaction log size and improving performance. Only drawback is that the point in time recover is not possible when bulk operations or index maintenance tasks are performed whenever database is in Bulk Logged recovery model.
Switching from Full to Bulk Logged Recovery Model
Switching from Full to Bulk Logged recovery model temporarily during the bulk operation will help you improve performance and will also help you reduce the size of transaction log as the transactions are minimally logged. However, if the data lose is not acceptable then you can switch to Bulk Logged only when you know that during the bulk operation there will be no users connected to the database to perform any operation. Before switching to Bulk Logged recovery model from Full, DBA should take the transaction log backup. Once the bulk operations have completed successfully; DBA without fail should switch back to Full database recovery model and perform a transaction log backup immediately.
Switching from Bulk Logged to Full Recovery Model
Once the bulk operations or index maintenance tasks have completed successfully DBA should switch back to Full recovery model. After switching back to Full recovery model from Bulk Logged, DBA should immediately perform the transaction log backup.
Switching from Bulk Logged or Full to Simple Recovery Model
It is not advisable to switch from Bulk Logged or Full to Simple recovery model for production databases. However if you are restoring the copy of production database to development or test server then after restoring the database you can switch the database recovery model to Simple. Database Administrator should perform transaction log back before switch to Simple recovery model as this will help to achieve point in time recovery for the database till that time. Once the database is in Simple recovery model you will not be able to perform transaction log backup as it is not supported.
How to Check the Current Recovery Model for Database
Database Administrator can check the current recovery model for a database by either executing TSQL or by using SQL Server Management Studio.
Using TSQL Query:
SELECT DATABASEPROPERTYEX(‘ADVENTUREWORKS’, ‘RECOVERY’) As [Recovery Model]
Using SQL Server Management Studio (SSMS)
Right click the AdventureWorks database under the Databases node in SSMS and select Properties, select the Options tab under Select a Page, to view the currently selected recovery model for the AdventureWorks database.