SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

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

Previous Page.. Begin Tutorial.. Next Page..

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.

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:-

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
How to Change the recovery model of SQL Server Database to BULK LOGGED Using SSMS
How to Change the recovery model of SQL Server Database to BULK LOGGED Using SSMS

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.. Begin Tutorial.. Next Page..

Related Articles

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement