Home / server / Questions / 162695
Accepted
AaronLS
Asked: 2010-07-22 08:12:27 +0800 CST

If logs are being truncated, why use Full recovery?

  • 772

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.

sql-server sql-server-2005

3 Answers

  1. 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

    • 3
  2. Best Answer

    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.

    • 3

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

    • 1