June 9, 2004 at 12:39 pm
I am in a kind of weird situation and I need some help from this forum....
I have 2 job agents on my production server, one for the full backup and the other for transaction logs backup..
The full backup job agent runs every night from 10:30pm upto 2:00am in the morning daily...and my transaction job agent starts from 2:00am in the morning upto 10:20pm night...that means that when my full backup is running then transaction job agent stops and once the full backup is done then my transaction job agent takes over from there...anyhow I'm peforming these operation for my backup purpose and for the logshipping....
My backup and logshipping was performing really great with no problems but 2 days ago...my full backup agent didn't run at all...so my logshipping got insync...so I have to restore the full backup manually and then apply the transaction logs one by one..but after applying my transaction logs I discovered that certain backupsets in my transaction log file....some of the backup sets are not continuous...that beats me....I don't know what to do...can anyone help me here..I appreciate that...
Muneeb.
June 9, 2004 at 1:56 pm
Exactly how were your backups done?
Did you create new backup files or were they appended to, or were they overwritten?
Here's what should have been done:
Full Backup
Transaction Log Backup with INIT (overwrite)
Transaction Log Backup (append)
Transaction Log Backup (append)
Full Backup (preferably a new file)
Transaction Log Backup with INIT
..etc...
Transaction logs apply to the most recent Full Backup, but you MUST have ALL the transaction log backups since the Full Backup.
Problem with the above suggestion is that if the Full Backup gets skipped (as happened to you), when the following Transaction Log backup happens it will overwrite the ones for the only Full Backup. Then you can't restore any transaction logs.
This is why some people will do their backups and append a date/time or copy the backup file to tape.
-SQLBill
June 9, 2004 at 1:58 pm
The failure to execute of your full backup should not have affected your logshipping in any way. Are you recreating logshipping on a nightly basis?
June 9, 2004 at 2:12 pm
Thanx for replying to my post Nicholas..
My logshipping is 24x7 every 10 mins of interval...and it's working fine...the only issue is the backup right now....and the log files that I have to restore on the logshipping server...my full backup is no sync with my transaction logs...
I think SQLBill knows what I trouble actually I'm having....
Thanx for your help I really appreciate it...
June 9, 2004 at 2:19 pm
Q: Exactly how my backups were done?
Ans: It's a full backup executed by a job agent every night at 10:30pm on a network shared drive before copying to a backup tape
Q: Did you create new backup files or were they appended to, or were they overwritten?
Ans: No, I use the INIT parameter with the backup command which works fine for me...we usually every night copy the backups on tape and in night the old backup file overwritten on the shared drive.
SQLBill, as you have suggested I follow the same procedure..and it works fine with me...till 2 days back and now I have this problem....
Any more thought on that...
Thanx.
Muneeb.
June 9, 2004 at 3:04 pm
Muneeb,
So, normal procedures are as follows?
1. full backup which overwrites the previous night's full backup
2. transaction log backups which overwrite the previous day's transaction log backups
???
If this is the case, you will need to get the tape which contains the first set of transaction log backups that got overwritten ( the ones that should be associated with the full backup that you have.)
You are going to need that full backup (so you need to move it to prevent it from being overwritten).
You will need all the transaction log backups that were taken since the full backup: the ones that got overwritten plus the ones you currently have.
Again, move these backups so that they won't get overwritten.
Once you have all the backups, you can restore your full backup, then restore all the transaction log backups in sequence.
The fact that you didn't get a full backup at some point won't impact this process. All of the transaction log backups since the full backup you have ARE associated with that full backup, even if you DID get a full backup sometime since the one in question.
Does all that make sense???
Steve
June 9, 2004 at 3:06 pm
One more point....
I would suggest that you modify your procedures such that you don't overwrite backups. Keep them as long as feasible (at least a couple of days), then create a process to automatically delete the old ones after the specified amount of time. That would help alleviate this type of situation.
Steve
June 11, 2004 at 4:28 am
Hi There,
Fairly new to DBA aspects of sql server myself, however, we run a 24 by 7 operation. I take a full backup at night, every four hours differential and every 10 mins transaction log backups. Each .BAK file created is moved to another server for online restore ( and backed up to tape ). By taking differential it would help you in reducing your recovery time, ie Restore full, latest differential and apply transaction log backups post last differential.
Regards.
Derek
June 11, 2004 at 10:25 am
Hi all,
Thanx to everyone whoever contributed to answer my question...these suggestion and guildlines helped me a lot..however, I found out what was causing the problem...its was a little and silly mistake that made my past 10 days hell...but now everything is perfect....later I found out that the 2 transaction job agents instead of appending in different transaction log file for 2 different database was writing in the same file at the same time and which was causing my transaction log out of sync....well we learn by our experiences..
Thanx a lot..
Muneeb.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply