November 27, 2008 at 7:16 am
Paresh Prajapati (11/27/2008)
so, what about, if have recover data up to last transaction log backup of day1 before full backup of second day?Need to restore full backup of fisrt day then apply all trn log backup which are generated after full backup of day 1 completed?
or
Need to restore full backup of fisrt day then apply all trn log backup which are generated since full backup of day 1 started?
In that case the first option would be correct.
[font="Verdana"]Markus Bohse[/font]
November 27, 2008 at 10:12 am
Julio Conejo (11/26/2008)
With this example in mind, the full started at 12am, and finished at 5 am, but the chief tells us that at 3:01 am one stupid employee did a worng process and we need to restore the state of the database at 3 am. Is there any way to go back to a time where the full backup was in process?Thanks
Excuse me for my english but its not my native language
Here is the change in thread. Julio added the constraint that a user made a mistake at 3:01 AM, while the backup was running, and how you would restore the database to 3:00 AM (prior to the user making the mistake).
In this scenerio, you can't use the backup that was in progress.
November 27, 2008 at 10:16 am
Paresh Prajapati (11/27/2008)
can we restore as full backup(#1) + tran log backup (#7 through #24) + tran log backup(#26 and # 27 and #28 (with recovery))?Bec in betwen them one full backup of day2 is also there ?
So, after restoration of trn log backup (# 24) of day 1 ..will it allow to restore trn log backup (# 26) of day 2, enen though without full backup (# 25) ?
Yes. Let us take another view of this. Let us say that full backup #25 was accidently deleted or the file was corrupted due to a hardware failure, but you have all the other backups listed, and you needed to restore to 6:00 AM on day 2. You could do this by following the same steps outlined to restore to 3:00 AM, you just need to restore the additional t-log backups making sure you use NORECOVERY on all restores except the last, where you would use RECOVERY.
November 27, 2008 at 10:34 pm
Is it allowed to resore full backup first and restore all tran log backups which are generated after full backup of day1? (without restoring trn log backups generated between full backup started and completed)...and u r talking about to restore full backup and then directly tran log backup generated after full backup completed..
i don't belive , we can restore this way..
bec, all tran log backups shoul be sequential..
November 28, 2008 at 12:45 am
MarkusB (11/27/2008)
HanShi (11/27/2008)
MarkusB (11/27/2008)
No, in your case if you want to restore everything until the last tlog backup (#28) you should start with the last full backup #25 and then add the remaing tlog backups.You are wrong. You do NOT use backup #25 because this backup was NOT completed when the wrong action took place.
HanShi,
I don't agree with you. In the original post there's no indication when a wrong action has happened. From what I can see the original request was to restore up to the last taken tlog backup.
Oops, I think I have to apologize.
I didn't notice that the "original post" I was referring to was just the first post on page 2. When I openend it and saw just one posting I assumed it was a new one. So I didn't look at all the discussions before that.
[font="Verdana"]Markus Bohse[/font]
November 28, 2008 at 1:23 am
MarkusB (11/28/2008)
MarkusB (11/27/2008)
HanShi (11/27/2008)
MarkusB (11/27/2008)
No, in your case if you want to restore everything until the last tlog backup (#28) you should start with the last full backup #25 and then add the remaing tlog backups.You are wrong. You do NOT use backup #25 because this backup was NOT completed when the wrong action took place.
HanShi,
I don't agree with you. In the original post there's no indication when a wrong action has happened. From what I can see the original request was to restore up to the last taken tlog backup.
Oops, I think I have to apologize.
I didn't notice that the "original post" I was referring to was just the first post on page 2. When I openend it and saw just one posting I assumed it was a new one. So I didn't look at all the discussions before that.
so ur assumption of backup is wrong..Now what ur idea as upper previous post?
November 28, 2008 at 2:11 am
Lynn Pettis (11/26/2008)
Scenerio:Day 1 Day2
12:00 AM Full backup started #1 Full backup started #25
1:00 AM T-Log backup (< 1 min) #2 T-Log backup (< 1 min) #26
2:00 AM T-Log backup (< 1 min) #3 T-Log backup (< 1 min) #27
3:00 AM T-Log backup (< 1 min) #4 T-Log backup (< 1 min) #28 At 3:01, users makes mistake
4:00 AM T-Log backup (< 1 min) #5 T-Log backup (< 1 min) #29
5:00 AM T-Log backup (< 1 min) #6 Full Backup #1 Completes T-Log backup (< 1 min) #30 Full Backup #25 Completes
6:00 AM T-Log backup (< 1 min) #7 T-Log backup (< 1 min) #31
7:00 AM T-Log backup (< 1 min) #8 T-Log backup (< 1 min) #32
8:00 AM T-Log backup (< 1 min) #9 T-Log backup (< 1 min) #33
9:00 AM T-Log backup (< 1 min) #10 T-Log backup (< 1 min) #34
10:00 AM T-Log backup (< 1 min) #11 T-Log backup (< 1 min) #35
11:00 AM T-Log backup (< 1 min) #12 T-Log backup (< 1 min) #36
12:00 PM T-Log backup (< 1 min) #13 T-Log backup (< 1 min) #37
1:00 PM T-Log backup (< 1 min) #14 T-Log backup (< 1 min) #38
2:00 PM T-Log backup (< 1 min) #15 T-Log backup (< 1 min) #39
3:00 PM T-Log backup (< 1 min) #16 T-Log backup (< 1 min) #40
4:00 PM T-Log backup (< 1 min) #17 T-Log backup (< 1 min) #41
5:00 PM T-Log backup (< 1 min) #18 T-Log backup (< 1 min) #42
6:00 PM T-Log backup (< 1 min) #19 T-Log backup (< 1 min) #43
7:00 PM T-Log backup (< 1 min) #20 T-Log backup (< 1 min) #44
8:00 PM T-Log backup (< 1 min) #21 T-Log backup (< 1 min) #45
9:00 PM T-Log backup (< 1 min) #22 T-Log backup (< 1 min) #46
10:00 PM T-Log backup (< 1 min) #23 T-Log backup (< 1 min) #47
11:00 PM T-Log backup (< 1 min) #24 T-Log backup (< 1 min) #48
Using the above:
When users didn't do a wrong transaction and you want to restore the complete database to the point of day 2, 11:00PM you have 2 options:
option 1 (fastest): restore full backup #25 and restore all log-backups #31 to #48
option 2 (if backup #25 failed): restore full backup #1 and restore all log-backups #7 to #24 and #26 to #48
If users did do a wrong transaction at 3:01AM on day 2 (during the full backup) and you want to restore the database to the time of day 2, 3:00AM you have only one option:
option 1: restore full backup #1 and restore all log-backups #7 to #24 and #26 to #28
If users did do a wrong transaction at 7:01AM on day 2 (after the full backup) and you want to restore the database to the time of day 2, 7:00AM you have again two options:
option 1 (fastest): restore full backup #25 and restore all log-backups #31 to #32
option 2 (if backup #25 failed): restore full backup #1 and restore all log-backups #7 to #24 and #26 to #32
November 28, 2008 at 3:13 am
good to understand...
ok, so #26 to #28 are part of full backup 1..
means whenever we have to recover data before full backup of day 2, we haveto require these also..
when #26 to #28 occur, will it truncate inactive part?
November 28, 2008 at 7:40 am
Okay, you don't have to take my word on this, in fact don't. Test it for yourself on a test server. Nothing beats actually doing it to be sure what you are trying to do is correct. Once you have done the testing, let us know your results.
November 28, 2008 at 9:52 pm
I have tried , fot that i have apply full backup first and then apply all log backups which are generated during and after full backup..
December 5, 2008 at 10:04 pm
Is there any query to Determine the sequence of all full, diff and transactional log backups?
How we can find sequence of those backups?
Suppose i have 5 full backups , 15 diff backups and more transactional loag backup..
So, restoration time, i have to know the sequence of backups..means which backups we have to apply in sequence.
HOW?
December 5, 2008 at 10:38 pm
I can't answer you last question, but I can tell you that you don't need the t-log backup(s) that are run while a full backup is executing. I recently had a request to restore a production database in a development environment. While the full backup I used to restore from was running, a t-log backup also ran. I restored the full backup with norestore and when completed, I attempted to restore the t-log backup created while the full backup ran, and this restore failed. The restore of subsequent t-log files created after the full backup, however, completed without error.
December 6, 2008 at 12:32 am
so, your meaning is we should not apply transaction log backups which are generated during full backups, only apply which are generated after full backup..otherwise it will give error.
My query is all transaction log backups(generated after full backup execpt during genetared) are sequential ?
or
all (during and after generated) transaction log backup are sequential?
If first option is right then what about transaction backup which generated during full backup,
are they sequential of previous full backup?
December 6, 2008 at 1:54 am
Yes.
You need the t-log backups generated during the full backup if you are restoring from a full backup taken EARLIER than backup running at the time the t-log backups were generated.
If I remember correctly that was described earlier in this thread.
December 6, 2008 at 4:19 am
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply