Connect With

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter

Trending SQL Server Tips

SQL Server BULK-LOGGED Recovery Model Step by Step Tutorial with Examples

Read Comments   |   Related Tutorials: SQL Server Database Backup Options > Next Topic: Different Types of Backups

Page 5 / 16

Click here to read this tutorial from the beginning...


When a database is configured to use a BULK-LOGGED Recovery Model then SQL Server will log minimal amount of information for operations such as SELECT INTO, BULK INSERT, BCP, CREATE INDEX, ALTER INDEX, and REBUILD INDEX etc within the transaction log file there by reducing the transactional log file size. The Point in Time recovery of the database is possible only if the last transaction log doesn’t have any BULK-LOGGED operations.

It is advisable to switch the recovery model from FULL to BULK-LOGGED while performing any of the above mentioned Bulk Logged operations as this will help to reduce the transaction log file growth and thereby improve the database performance. However, as like in FULL recovery model the transaction log will continue to grow until the log file is backed up.

Note: - As a Best Practice database administrator should make sure that all the user databases in a Production environment are configured to use FULL recovery model and you are using a combination of Full, Differential and Transactional Log backups as part of database backup plan to avoid any data loss.

When to choose BULK-LOGGED Recovery Model for a database in SQL Server?

  • Data is critical for the organization, but you would like to reduce the size of transactional log file during SELECT INTO, BULK INSERT, BCP, CREATE INDEX, ALTER INDEX, and REBUILD INDEX etc operations and can afford any data loss caused due to the execution of any of the before mentioned operations.
  • One can still achieve Point In Time recovery of a database as long as you have latest transactional log backup and it doesn't have any BULK-LOGGED transactions.
  • It can be used when you run batch jobs or during time when you perform routine maintenance. However, note that change in recovery model will break the backup chain hence make it a point to take full backup after changing the recovery model.

Different types of backups which can be performed when a database is in BULK-LOGGED Recovery Model are:-

  • Full Backup
  • Differential backups
  • Transaction log backups
  • File / FileGroup backups
  • Partial backups
  • Copy-Only backups

How to Change Database Recovery Model to BULK-LOGGED Using TSQL Command


How to Change Database Recovery Model to BULK-LOGGED Using SSMS

  • Connect to SQL Server Instance using SQL Server Management Studio
  • Expand Database Node and then right click the user Database and select Properties from the drop down menu
  • Click Options Page on the right side pane as highlighted in the below snippet
  • Under Recovery Model choose BULK-LOGGED and click OK to save

How to Change the recovery model of SQL Server Database to BULK LOGGED Using SSMS

Note: - Changing the recovery model of a database will break the backup chain. Hence, as a Best Practice one should immediately take the full backup of the database after changing the recovery model.

Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.

Previous Page.. Next Page..

Learn More...

Last Updated On: Feb 12, 2014

Share this Article

Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips

Please leave your Valuable Comment or Let us know how this article helped you: