Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Trying to get a sense of the optimal setup in our environment.

I have a large SQL05 DB (500+GB) that is log shipped to a read-only system over a WAN. I'm currently running in FULL recovery model, with log dumps every 5 minutes during peak hours and 15 minutes off hours.

It's my understanding that BULK_LOGGED recovery model will allow me to maintain log shipping integrity, at the cost of not being able to do a point-in-time recovery to a time within the period covered by the backup.

Given the frequency of our log dumps, does it make sense to run FULL at all -- since I can restore with the BULK_LOGGED log dumps to a 5-minute recovery window? Or do I misunderstand how this works?

share|improve this question

migrated from Jan 19 '10 at 3:54

This question came from our site for professional and enthusiast programmers.

Is the log shipping for reporting or for BCP/DR mostly?

If reporting, yes. If for BCP/DR, then it depends on the requirements (ie what data loss is business prepared for)

Have you looked at DB mirroring? This allows you full backups on the primary and "high performance" allows lazier writes to the mirror which may achieve the same result. Log shipping is a manual failover process anyway, so you lose nothing with this mirroring mode.

share|improve this answer
Log shipping is for BCP/DR. As for what we're prepared for -- well, if BULK_LOGGED won't give us a database that can "step in when absolutely required", it won't work for us. DB mirroring will not work in our environment (too many tables with too many columns, or so I'm told). – paulland Jan 18 '10 at 22:30

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.