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.

This is Part 5 of 16 Part SQL Server Database Backup Tutorial. Click here to read it from the beginning….

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.

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

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

Related Articles

• SQL Server Database Backup Tutorial with Examples
• Different SQL Server Recovery Models Step by Step Tutorial with Examples
• SQL Server SIMPLE Recovery Model Step by Step Tutorial with Examples
• SQL Server FULL Recovery Model Step by Step Tutorial with Examples
• SQL Server BULK-LOGGED Recovery Model Step by Step Tutorial with Examples
• Different Types of SQL Server Backups
• Permissions Required to Take Database Backup in SQL Server
• FULL Database Backups in SQL Server Step by Step Tutorial with Examples
• DIFFERENTIAL Database Backups in SQL Server Step by Step Tutorial with Examples
• TRANSACTION LOG Backups in SQL Server Step by Step Tutorial with Examples
• COPY_ONLY Backup in SQL Server Step by Step Tutorial with Examples
• MIRRORED Backup in SQL Server Step by Step Tutorial with Examples
• FILE Backup in SQL Server Step by Step Tutorial with Examples
• FILEGROUP Backup in SQL Server Step by Step Tutorial with Examples
• PARTIAL Backup in SQL Server Step by Step Tutorial with Examples
• TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples