mssqltips logo

SQL Server Bulk-Logged Recovery Model



By:
Overview

The "Bulk-logged" recovery model sort of does what it implies.  With this model there are certain bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc... that are not fully logged in the transaction log and therefore do not take as much space in the transaction log. 

Explanation

The advantage of using the "Bulk-logged" recovery model is that your transaction logs will not get that large if you are doing large bulk operations and it still allows you to do point in time recovery as long as your last transaction log backup does not include a bulk operation.  If no bulk operations are run, this recovery model works the same as the Full recovery model.  One thing to note is that if you use this recovery model, you also need to issue transaction log backups otherwise your database transaction log will continue to grow.

Here are some reasons why you may choose this recovery model:

  • Data is critical and you want to minimize data loss, but you do not want to log large bulk operations
  • Bulk operations are done at different times versus normal processing.
  • You still want to be able to recover to a point in time

Type of backups you can run when the data is in the "Bulk-logged" recovery model:

  • Complete backups
  • Differential backups
  • File and/or Filegroup backups
  • Partial backups
  • Copy-Only backups
  • Transaction log backups

Set SQL Server Bulk-Logged Recovery Model using T-SQL

ALTER DATABASE dbName SET RECOVERY recoveryOption
GO

Example: change AdventureWorks database to "Bulk-logged" recovery model

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED
GO

Set SQL Server Bulk-Logged Recovery Model using Management Studio

  • Right click on database name and select Properties
  • Go to the Options page
  • Under Recovery model select "Bulk-logged"
  • Click "OK" to save
change database bulk-logged recovery model

Last Update: 2/22/2019




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, April 23, 2019 - 11:19:34 AM - Gabor Back To Top

From MSDN to BULK-LOGGED model: "Can recover to the end of any backup. Point-in-time recovery is not supported."



download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools