4

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.

  • What do you think?
  • Is this a good idea?
  • Or it is risky?
7

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy