Restoring the DB Backup

Question

Restoring the DB Backup

dakshinamurthy-655138

SSCertifiable

Points: 5172
More actions
July 26, 2012 at 3:42 am

#264885

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Divine Flame SSCoach Points: 15943 More actions · Answer 1

dakshinamurthy-655138 (7/26/2012)
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

Yes, you can. Restore the database point-in-time to 1:00 PM & you will get the data upto that time only.

Sujeet Singh

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 2

Will you please let me know how this can be acheived. Please..

Divine Flame SSCoach Points: 15943 More actions · Answer 3

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.

Sujeet Singh

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 4

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

Ant-Green SSC Guru Points: 113499 More actions · Answer 5

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

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 6

dakshinamurthy-655138

SSCertifiable

Points: 5172

July 26, 2012 at 6:00 am

#1517857

Yes exactly

Ant-Green SSC Guru Points: 113499 More actions · Answer 7

You will need to follow the whole restore process again

Full NoRecovery

Diff 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.

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 8

dakshinamurthy-655138

SSCertifiable

Points: 5172

July 26, 2012 at 6:03 am

#1517860

Thanks for the info

Daxesh Patel Hall of Fame Points: 3208 More actions · Answer 9

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

durai nagarajan SSCoach Points: 15778 More actions · Answer 10

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

Daxesh Patel Hall of Fame Points: 3208 More actions · Answer 11

Daxesh will the DB in a readable poisition if it is in stand by?

Yes