November 22, 2008 at 1:01 am
My Database has take time 5 hrs for FULL Backups.When full backup in process at thattime supoose i take transactional nbackups, then what happen?
November 22, 2008 at 1:26 am
I'm not really sure what you are asking here. With SQL Server 2005 and later, full backups no longer block transaction log backups from running. Any transaction log backups completed during a full backup do not delete inactive transactions.
Does this help in any way?
November 22, 2008 at 2:37 am
ok, suppose i take full backup which take 5 hour to backup...
at that time user has done some activity in table from applicatiion, and transaction log backup i have take hourly.
Now, i have to cover all data up to all data from all use changes in 5 hrs.
so i have to restore only full backup or trn log backup also...
Because during full backup user has change data..
???
also My query if first i need to apply full backup..then all log backups after full backup.or apply only full backup..(Just i am talking about trn log genereated during full backup..i have require data time when the full backup finished)
November 22, 2008 at 10:12 am
I'm sorry, but I really don't understand the question, but here is a shot at it.
At 12:00 AM a full backup is started that doesn't finish until 5:02 AM. Transaction log backups are taken at 1:00 AM, 2:00 AM, 3:00 AM, 4:00 AM, and 5:00 AM (with the transaction log finishing prior to the full backup). Users are working during this time updating the database.
Something happens at 5:03 AM, just after the full backup completes requiring a restore of the database.
All that needs to be restored is the full backup. It is complete upto the time is finished, so it has all the work entered between 12:00 AM and 5:02 AM.
Does this answer your question?
November 22, 2008 at 10:58 am
Thanks Lynn Pettis. Atleast it cleared my doubts. Thanks Paresh for asking such a question.
November 26, 2008 at 5:46 pm
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
November 26, 2008 at 7:40 pm
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
Yes, but I would not use the full backup that was in progress when the user made the mistake. I'm not sure if it would work.
Here is how I would recover the database to 3:00 am.
Restore the most recent previous full backup with norecovery.
Restore the most recent differential backup taken after that (if one), again with norecovery.
Restore all the transaction log backups taken after the last differential backup, or the full backup if there was no differential backup) with norecovery on all t-log restores, except the last one (the one taken at 3:00 am, which would be done with recovery.
Hope this helps.
November 26, 2008 at 10:46 pm
So, as per ur side, we need to restore full backup first then restore all trn log backups up to point of time witch we have required up to..is it ok?
But my question is when full backup start at 12 am to 5 am.. during that trn log backup started hourly at 1 ,2,3,4,5 am ok.
when one use has updated wrong data did mistake at 3 am..
so, at that time i need to first restore full backup which is completed on 5 am, then restore all tran log backups up to 3 am..ok
so, full backup not contain those wrong updated data?.so when i restore full backup..it obvious contain those wrong updated data..bac it is running up to 5 am?
November 26, 2008 at 11:19 pm
No. I said to ignore that backup. 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:
Want to restore to 3:00 AM on Day 2
Restore with norecovery Full backup #1
Restore with no recovery t-log backups #7 through #24, and #26 and # 27
Note, it might be t-log #6, but it depends on when the full backup actually completed, before or after #6
Restore with recovery t-log backup #28
This will get us back to the point prior to the user mistake.
Understand now?
November 26, 2008 at 11:34 pm
Paresh Prajapati (11/26/2008)
So, as per ur side, we need to restore full backup first then restore all trn log backups up to point of time witch we have required up to..is it ok?But my question is when full backup start at 12 am to 5 am.. during that trn log backup started hourly at 1 ,2,3,4,5 am ok.
when one use has updated wrong data did mistake at 3 am..
so, at that time i need to first restore full backup which is completed on 5 am, then restore all tran log backups up to 3 am..ok
so, full backup not contain those wrong updated data?.so when i restore full backup..it obvious contain those wrong updated data..bac it is running up to 5 am?
You got it wrong. If the a full backup started at 12:00 and ended at 5:02 and during that time I took 6 log backups (at 12:00, 1:00, 2:00, 3:00, 4:00, 5:00), I’ll be able to restore to the point of time 3:01, but not with the full backup that was running during the time that the log backup was running. I’ll have to use the previous full backup that was done and then start restoring all needed differential and logs backups including the log backups that were taken during the next full backup.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 27, 2008 at 3:19 am
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) ?
November 27, 2008 at 3:29 am
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.
[font="Verdana"]Markus Bohse[/font]
November 27, 2008 at 4:31 am
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?
November 27, 2008 at 6:20 am
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.
November 27, 2008 at 7:15 am
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.
[font="Verdana"]Markus Bohse[/font]
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply