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.

AaronLS
  • 955
  • 1
  • 10
  • 22

3 Answers3

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

K. Brian Kelley
  • 9,034
  • 32
  • 33
  • +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 '10 at 10:42
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.

Doug Luxem
  • 9,612
  • 7
  • 50
  • 80
  • For this to work the Full backup must be done **after** the log truncation, correct? – AaronLS Jul 21 '10 at 16:34
  • Yes, but, why not just get rid of the truncate? – SqlACID Jul 21 '10 at 16:36
  • 1
    @Aaron You are correct, or perform a log backup before your full. – Doug Luxem Jul 21 '10 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 '10 at 16:47
1

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

SqlACID
  • 2,176
  • 18
  • 18