August 23, 2010 at 3:44 am
Hello there,
I have an error which I cannot seem to resolve. I have a daily Mon-Fri evening backup at 8pm and during the day I am doing t-log backups every 3 hours between 9 am and 6pm. Now come monday morning, the full friday evening backup is still there for the 9am monday morning t-log backup to do its stuff, but it fails with an error saying there is no data backup in place. This only happens on Monday mornings, all other mornings are fine. I'm running SQL 2005 SP2. Has anybody else come across this at some point?
Thank you for any advice.
Regards,
D.
August 23, 2010 at 4:11 am
The error means that between the time that the last full backup was run and this log backup, someone has either run BACKUP LOG ... WITH TRUNCATE ONLY or has switched from full recovery to simple and back.
Both of those break the log chain, meaning there's no full backup base for the log backups. You'll need to take a full or diff backup before you can run log backups again.
I would suggest that you look through jobs over the weekend and find out where that log truncation occurred. My guess is that there's an index rebuild job that's either switching to simple recovery or truncating the log. Also, please read through this - Managing Transaction Logs[/url]
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
August 23, 2010 at 4:42 am
Hi Gail,
Of course you were spot on, I did not notice a truncate-only job running under the agent between Fridays full and Monday's T-log backup.
Regards,
D.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply