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

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.

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 Backup
• Transaction Log Backup
• File / FileGroup Backup
• Partial Backup
• Copy-Only Backup

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

ALTER DATABASE MyTechMantra SET RECOVERY BULK-LOGGED
GO

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