Changing database recovery model

Last Post 10 Jun 2011 04:25 AM by goaler. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
river1
New Member
New Member

--
09 Jun 2011 12:54 AM

Hi,

I am thinking in Changing my SQL Server 2000 and 2005 databases from simple recovery model to full recovery model.

It makes no sense that this databases are in simple recovery model.


Do i need to test my apps before changing afetr changing the recovery model?

I think that it is not necessary because it's just a change of recovery model, but still i would like your advice.

Thank you.


P.S ,

    I have some bulk insert during the end of the day in this databases, i think that this was why the person that creted this databases let them in simple recovery model (for the log not to grow). 

If i change to full recovery model but do regular transaction log backups the log will be at the same size as if it as in simple recovery model?

gunneyk
New Member
New Member

--
09 Jun 2011 05:16 AM
If you change to FULL recovery mode you need to do regular log backups regardless of the types of operations that occur. The apps will not know the recovery model has changed and should not be affected. As for the BulkInserts though that depends on how they are done as to if they are taking advantage of a minimally logged load or not. All operations log to the transaction log regardless of the recovery mode. Some bulk type operations can take advantage of what is called a minimally logged operation which only logs the page numbers that were touched by the operation and not all the row by row stuff that normally would be logged. But a Bulk Insert by itself does not qualify for a minimally logged operation without a handful of other requirements. See booksonline under minimally logged operations for a more detailed explaination.
goaler
New Member
New Member

--
10 Jun 2011 04:25 AM
2 cents on this would be that testing yous applications is the least of your concerns, but I would focus on the potential disk space requirements with the switch.  The last post is correct on what you maybe looking at, but at the SQL Server level you need to think about the impact of what this will do to the requirements on the SQL Server itself and the amount of disk you may now need to have to implement a disaster recovery plan to allow for this new recovery model.
You are not authorized to post a reply.

Acceptable Use Policy