patternsqlMinor
Full or bulk-logged recovery model?
Viewed 0 times
fullbulkrecoveryloggedmodel
Problem
My database is about 10 GB and I use full recovery model.
Sometimes I have to add a lot of data at once and during that time, I change the recovery model from full to bulk-logged.
Sometimes I have to add a lot of data at once and during that time, I change the recovery model from full to bulk-logged.
- What do you think?
- Is this a good idea?
- Or it is risky?
Solution
You probably misunderstood the recovery model concept. From BOL Doc
A recovery model is a database property that controls how transactions
are logged, whether the transaction log requires (and allows) backing
up, and what kinds of restore operations are available
Recovery model controls the logging and there by how much data you can recover. Only certain transactions which would be minimally logged would be minimally logged all other transactions would be fully logged in bulk logged recovery model. So it depends on how much data you can loose keeping that in mind choose recovery model. You should read Backup under BL recovery model.
If you are performing a bulk logged operation and dont really care about point in time recovery during the period the BL operation runs go ahead change recovery model to bulk logged. But read considerations before changing to BL recovery Model
Risk.
If you perform operation that are minimally logged then you loose point in time recovery for the time period this transaction is running. After this transaction finishes and other transaction starts which is fully logged you can perform point in time recovery. You must read Understanding Logging and recovery in SQL Server
A recovery model is a database property that controls how transactions
are logged, whether the transaction log requires (and allows) backing
up, and what kinds of restore operations are available
Recovery model controls the logging and there by how much data you can recover. Only certain transactions which would be minimally logged would be minimally logged all other transactions would be fully logged in bulk logged recovery model. So it depends on how much data you can loose keeping that in mind choose recovery model. You should read Backup under BL recovery model.
If you are performing a bulk logged operation and dont really care about point in time recovery during the period the BL operation runs go ahead change recovery model to bulk logged. But read considerations before changing to BL recovery Model
Risk.
If you perform operation that are minimally logged then you loose point in time recovery for the time period this transaction is running. After this transaction finishes and other transaction starts which is fully logged you can perform point in time recovery. You must read Understanding Logging and recovery in SQL Server
Context
StackExchange Database Administrators Q#128249, answer score: 7
Revisions (0)
No revisions yet.