August 23, 2010 at 8:08 am
Hello all,
I have SQL 2008 and Im having problems with the Transaction Logs backups. Im using the Maitenance Plan in the Management Studio to run the backups. Im getting the following error whenever my Transaction Log backup runs:
"BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I have set to where it should backup to another drive on the server. The ".bak" file shows in the drive but I get the error in the Management Studio. There is also a current database backup that runs with no errors hours before the Transaction Log job begins to run. Can someone please help me out and tell me what I am doing wrong. I appriciate any help that I can get.
August 23, 2010 at 8:16 am
If you get that error, you probably haven't taken the first full DB backup yet. You have to have at least one full DB backup before taking log backups, as log backups are useless without being applied to the initial DB backup when doing restores.
August 23, 2010 at 8:19 am
At some point between when the full backup ran and when you are trying to run the log backup, someone has switched the database from full recovery to simple recovery and back. (Backup log ... with truncate only does the same, but it does not owrk on SQL 2008)
Both of those break the log chain and you will not be able to run log backups until another full/diff backup is taken to restart the log chain.
I suggest that you have a look through the jobs that run between the full backup and the log, see if any are doing either or those two things. Most common one I've seen is the index rebuild job where people either switch to simple or truncate the log because they don't know how to handle the log growth.
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 12:16 pm
Thanks for the replys guys!! I re-ran the full backup and then the Transaction log backup and it completed sucessfully!!
Another question if I can ask. What would be a proper schedule for a DB and Transaction log backup? Im new to teh SQL world and Im wondering what is my best backup schedule. For right now I have it set for a daily Full Simple backup at 12am nightly and the Transaction Logs every 3 hours. Is that ok or is there a better way?
Thank you for all the help!! Lunch is on me next time! lol
August 23, 2010 at 12:25 pm
Depends upon the size of the DB and how critical the recovery of data loss is. For small to medium systems, my rule of thumb is nightly full and transaction logs every 15 minutes to one hours, depending upon how painful data loss is. For large systems, I can't do a full backup daily in the maintenance window. So I do weekly full and daily differential for those. Transaction log backups are unchanged. But there is no rule beyond what meets your needs.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply