SQL Server BULK-LOGGED Recovery Model Step by Step Tutorial with Examples
Related Tutorials: SQL Server Database Backup Options>
Next Topic: Different Types of Backups
Page 5 / 16
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
ALTER DATABASE MyTechMantra SET RECOVERY BULK-LOGGED
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
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.
- Different Types of SQL Server Backups
- Permissions Required to Take Database Backup in SQL Server
- FULL Database Backup in SQL Server Step by Step Tutorial with Examples
- DIFFERENTIAL Database Backups in SQL Server Step by Step Tutorial with Examples
- TRANSACTIONAL LOG Backups in SQL Server Step by Step Tutorial with Examples
Last Updated On: Feb 12, 2014
Please leave your Valuable Comment or Let us know how this article helped you: