February 23, 2015 at 5:30 am
Bouke Bruinsma (2/23/2015)
I wouldn't dare questioning your or Gails remarks. But I could give many examples where a database is in full recovery mode without any backup strategy in place.
And I can give you many, many examples of cases where databases are in full recovery and have a very nice set of full and even diff backups and the log is growing out of control. However I can only remember one case where there were no backups at all. I do a lot of SQL Server audits. It's my job and has been for the last ~6 years.
But it's completely meaningless how many examples of the two we can give. The fact is that full backups do not truncate (mark reusable) the log of a database that's in full recovery, so saying they need to take a full backup ASAP is incorrect. They need to either switch the database into simple recovery model permanently (if the SLAs allow) or schedule log backups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2015 at 6:42 am
Bouke Bruinsma (2/23/2015)
I wouldn't dare questioning your or Gails remarks. But I could give many examples where a database is in full recovery mode without any backup strategy in place. The log file grows and grows in time then.
If the database is in full recovery mode and there is no backup strategy at all, meaning no backups, neither full or log, are taken, wouldn't the log auto-truncate itself untill first full backup?
February 23, 2015 at 6:56 am
Kutang Pan (2/23/2015)
Bouke Bruinsma (2/23/2015)
I wouldn't dare questioning your or Gails remarks. But I could give many examples where a database is in full recovery mode without any backup strategy in place. The log file grows and grows in time then.If the database is in full recovery mode and there is no backup strategy at all, meaning no backups, neither full or log, are taken, wouldn't the log auto-truncate itself untill first full backup?
Yes it will. Until the first full backup is taken a DB in full recovery is actually in a pseudo-simple recovery model. First full backup initialises the log chain and from that point it's really in full recovery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2015 at 7:22 am
GilaMonster (2/23/2015)
Bouke Bruinsma (2/23/2015)
I wouldn't dare questioning your or Gails remarks. But I could give many examples where a database is in full recovery mode without any backup strategy in place.And I can give you many, many examples of cases where databases are in full recovery and have a very nice set of full and even diff backups and the log is growing out of control. However I can only remember one case where there were no backups at all. I do a lot of SQL Server audits. It's my job and has been for the last ~6 years.
But it's completely meaningless how many examples of the two we can give. The fact is that full backups do not truncate (mark reusable) the log of a database that's in full recovery, so saying they need to take a full backup ASAP is incorrect. They need to either switch the database into simple recovery model permanently (if the SLAs allow) or schedule log backups.
Point taken, thanks for pointing this out (also the next post about the pseudo simple recovery model until the first full backup is taken). My point was that a full backup needs to be taken so that there indeed is a recent full backup. If the last full backup has been a long time ago, a system failure would mean traumatic loss of data and work.
I would be interested in the OP's comment on the current and the future backup strategy.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply