July 26, 2012 at 3:42 am
Hi
I have a scenario,
I have the following backups with me
1) Full DB backup at 12 AM tonight,
2) Diff Backup at 6 AM morning
3) Tlog Bacup at 8 AM, 10 AM, 12 PM and 2 PM
now i have a query, i have done some operation at 1.30 PM,
now i have the backup till 12 pm or till 2 pm,
can i get the intermediate data i.e., at 1 PM
With Regards
Dakshina Murthy
July 26, 2012 at 3:55 am
dakshinamurthy-655138 (7/26/2012)
HiI have a scenario,
I have the following backups with me
1) Full DB backup at 12 AM tonight,
2) Diff Backup at 6 AM morning
3) Tlog Bacup at 8 AM, 10 AM, 12 PM and 2 PM
now i have a query, i have done some operation at 1.30 PM,
now i have the backup till 12 pm or till 2 pm,
can i get the intermediate data i.e., at 1 PM
With Regards
Dakshina Murthy
Yes, you can. Restore the database point-in-time to 1:00 PM & you will get the data upto that time only.
July 26, 2012 at 3:58 am
Will you please let me know how this can be acheived. Please..
July 26, 2012 at 4:28 am
dakshinamurthy-655138 (7/26/2012)
Will you please let me know how this can be acheived. Please..
You have following backup files with you:
dakshinamurthy-655138 (7/26/2012)
1) Full DB backup at 12 AM tonight,2) Diff Backup at 6 AM morning
3) Tlog Bacup at 8 AM, 10 AM, 12 PM and 2 PM
In that case, restore your database in following order:
1. Restore the full backup of 12 AM with NORECOVERY
2. Restore the differential backup of 6 AM with NORECOVERY
3. Restore log backup of 8 AM with NORECOVERY
4. Restore log backup of 10 AM with NORECOVERY
5. Restore log backup of 12 PM with NORECOVERY
Once this has been done you need to restore the last log backup file (of 2 PM) specifying RECOVERY along with STOPAT.
i.e. your syntax should look like :
RESTORE LOG YourDatabaseName FROM DISK ='YourLogBackupFileName'
WITH RECOVERY, STOPAT='2012-07-26 13:00:00.000'
Note the STOPAT keyword in above syntax. It allows you to restore the database upto a certain point-in-time. You need to mention the date & time over there.
July 26, 2012 at 5:56 am
Thanks for the Quick Response.
It worked for us Successfully.
Again i need one more clarification, assuming that i used the command STOPAT and then if i have iterate the timings (STOPAT) with Recovery option. Do let me know if this can be done.
Regards
Dakshina Murthy
July 26, 2012 at 5:59 am
How do you mean iterate the timings?
Do you mean restore to 13:00, then restore to 13:05, then restore to 13:10 etc
July 26, 2012 at 6:00 am
Yes exactly
July 26, 2012 at 6:02 am
You will need to follow the whole restore process again
Full NoRecovery
Diff NoRecovery
TX NoRecovery
TX NoRecovery
TX NoRecovery
TX Recovery, STOPAT '2012-07-26 13:05:00'
Once the DB has been put into recovery you cannot apply any other logs to it.
July 26, 2012 at 6:03 am
Thanks for the info
July 26, 2012 at 8:07 am
dakshinamurthy-655138 (7/26/2012)
Thanks for the Quick Response.It worked for us Successfully.
Again i need one more clarification, assuming that i used the command STOPAT and then if i have iterate the timings (STOPAT) with Recovery option. Do let me know if this can be done.
Regards
Dakshina Murthy
Optionally you can use STANDBY instead of NORECOVERY if you want to read data inbetween each restore.
Then restore tlog with STOPAT multiple time with different time (you can only go forward)
e.g.
RESTORE ..... from Disk='logfile1.trn' ....... STOPAT '2012-07-26 13:00:00'
RESTORE ..... from Disk='logfile1.trn' ....... STOPAT '2012-07-26 13:05:00'
RESTORE ..... from Disk='logfile1.trn' ....... STOPAT '2012-07-26 13:10:00'
When you are using STANDBY, make sure backup and resote server has same version
July 26, 2012 at 8:24 am
Daxesh Patel (7/26/2012)
dakshinamurthy-655138 (7/26/2012)
Thanks for the Quick Response.It worked for us Successfully.
Again i need one more clarification, assuming that i used the command STOPAT and then if i have iterate the timings (STOPAT) with Recovery option. Do let me know if this can be done.
Regards
Dakshina Murthy
Optionally you can use STANDBY instead of NORECOVERY if you want to read data inbetween each restore.
Then restore tlog with STOPAT multiple time with different time (you can only go forward)
e.g.
RESTORE ..... from Disk='logfile1.trn' ....... STOPAT '2012-07-26 13:00:00'
RESTORE ..... from Disk='logfile1.trn' ....... STOPAT '2012-07-26 13:05:00'
RESTORE ..... from Disk='logfile1.trn' ....... STOPAT '2012-07-26 13:10:00'
When you are using STANDBY, make sure backup and resote server has same version
Daxesh will the DB in a readable poisition if it is in stand by?
Regards
Durai Nagarajan
July 26, 2012 at 8:29 am
Daxesh will the DB in a readable poisition if it is in stand by?
Yes
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply