4

We are doing nightly full backups and noon differential backups. We use Full recovery model with SQL Server 2005, but logs are never backed up and are truncated(TRUNCATE_ONLY) after the full backup.

Restoring to a point in time is not a requirement, restoring to one of the nightly or noon backups is sufficient (Not my decision).

So the question at hand is, since they are throwing away the logs every night, is there any reason to not use Simple Recovery model? Is there any benefit to using Full Recovery model if we are throwing out the logs every night?

Thanks.

Improve this question

3 Answers 3

Reset to default
3

Although you say point in time is not a requirement, is 12 hours of data loss worth the disk space? Personally, I would leave the full recovery model, and then in the case of a failure I would restore the latest backup and replay the logs (which hopefully are on a separate disk). Obviously, this may depend on the log size.

Improve this answer
4
  • For this to work the Full backup must be done after the log truncation, correct?
    – AaronLS
    Jul 21, 2010 at 16:34
  • Yes, but, why not just get rid of the truncate?
    – SqlACID
    Jul 21, 2010 at 16:36
  • 1
    @Aaron You are correct, or perform a log backup before your full.
    – Doug Luxem
    Jul 21, 2010 at 16:44
  • @SQLAcid They are truncating the logs because the log file grows indefinitely and within a few days all of the DB logs together would consume all free disk space. If it were up to me I would backup the log files which would keep the log size down, but they stopped backing up logs because they claimed it was too much of a "strain" on the server.
    – AaronLS
    Jul 21, 2010 at 16:47
3

There's no real point for this database to be in full recovery mode. With the truncate you've effectively rendered it unusable for point-in-time recovery until the next full or differential takes place. BTW, TRUNCATE_ONLY is not present in SQL Server 2008. More here:

BACKUP LOG WITH NO_LOG - use, abuse, and undocumented trace flags to stop it

Improve this answer
1
  • +1: I agree. Point in time recovery is not a business requirement in this case. Just create and test the recovery process, so it's good to go if needed at the push of a button.
    – John Sansom
    Jul 22, 2010 at 10:42
1

Other than eating up disk space for logs, I see no benefit to Full Recovery

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .