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:
ALTER DATABASE <Database_Name> SET RECOVERY BULK_LOGGED
- Minimum log space is used by bulk-logged
- High Performance
- Comparatively less critical data
- 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
You may also need: Set Database To Simple Recovery Model in SQL Server
0 comments:
Post a Comment