Set Database To Bulk-logged Recovery Model in SQL Server

The concept of bulk-logged is pretty tricky when recovery model is into consideration. Bulk as the name says, does bulk transactions such as inserts, updates, imports, which will be captured on transaction log during database recovery. Unlike full recovery model, the Bulk-logged takes the minimal of the fully logged transactions. On a transaction-by-transaction in a database, there will be increased risk of data loss when set as bulk-logged recovery model. Bulk-logged can take a lot of space consumption if there are not transaction log backups created. However, the bulk-logged can help to recovery from point-in-time as far as there no transaction logs involved. Additionally, backups that can be created from the bulk-logged setting would be full backups, differential backups, COPY_ONLY backups and T-log backups.

Some of the reasons to go for Simple Recovery Model:

  1. Minimum log space is used by bulk-logged
  2. High Performance
  3. Comparatively less critical data
  4.  Possibility of point-in-time recovery


T/SQL script to set the database to bulk-logged recovery model:

ALTER DATABASE <Database_Name> SET RECOVERY BULK_LOGGED


Follow the below steps in SQL Server Management Studio graphic user interface:

1.      Right on the respective database and click “properties”
2.      Select “options” from “Select a page” side bar
3.      A drop is seen under “Recovery Model”. Select “Bulk-logged” from the drop down.
4.      Click “OK” to set the database to simple recovery model

The screenshot for the same tagged below:



You may also need: Set Database To Simple Recovery Model in SQL Server

0 comments:

Post a Comment