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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
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