In this article we explain you all about the Bulk Logged Recovery Model Available in SQL Server. Read to know how, when and why to use it.
The processes of backing up data and restoring backed up data in SQL Server happens in the Recovery models provided by the application. Recovery models in SQL Server are provided by default, and have been given for the very important purpose of controlling the maintenance of the transaction log. It is the recovery model that manages how the transactions are logged. It looks into aspects like; whether the transaction log is in the need of a backup, what are all the restore options that are available for the logs. SQL Server databases provide the users with three different kinds of recovery models; Full, Simple and Bulk – Logged. The first two models are the most common, and are usually used, so today we will be discussing only about the third one.
Bulk Recovery Model
If you dig deeper, you will understand that this is quite self explanatory. Under this model the user is given option of implementing several bulk operations, namely; BULK INSERT, SELECT INTO, CREATE INDEX and more. Operations which are not entirely logged inside the transaction log, yet also do not occupy a great amount of space within the log. One of the important points to remember is, if you wish the log space to not grow, you will have to perform transaction log backups, otherwise they won’t stop growing.
One of the greatest advantages of opting for the Bulk logged recovery model is its capacity to prevent the size of the transaction logs from growing too much, even if happen to be undertaking bulk operations. And although this model does not allow for ‘point in time’ recovery, you can do that too if you do not have any of the bulk operations performed in this model. Therefore, if you make use of this model, but still do not run any of the bulk operations it allows for, it will work much like the venerable full recovery model. However for completely avoiding data loss, it is best to invest in a specialized corrupted sql recovery tool.
When and why should you use the Bulk Recovery Model
As mentioned already, this model should be used when you have to undertake any of the given Bulk operations it allows for, and still ensure that the transaction log does not grow too much in size. If there are no bulk operations to undertake, you might as well use the full recovery model. Given below are a few other reasons as to why this model should be your choice.
- When the data you are using is important and yet you also want to avoid large operations from logging.
- Bulk operations in process but not at the same time as processing.
- You may still wish to perform point in time recovery.
The variety of backups allowed in this model are:
- Differential Backups
- Complete backups
- Copy only backups
- Partial backups
- Filegroup backups
- Transaction log backups
Using SSMS to set Bulk Recovery Model
Use the Right click option on the name of the database, and click on Properties- Move to Options, in the Recovery Model, click on ‘Bulk Logged’.
- Click on ‘OK’ to save changes
Use the Right click option on the name of the database, and click on PropertiesAuthor Introduction:
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including accdb recovery and sql recovery software products. For more information visit https://www.datanumen.com/
Leave a Reply