question

petermgonzales avatar image
petermgonzales asked

How do you get data back into the database after doing a restore point in time?


--backup history

myfullbackup.bak 20210202 0001

mydiffbackup.diff 20210202 1200

--updated some records at 1230

mylogbackup1.trn 20210202 1300

mylogbackup2.trn 20210202 1300

mytaillog.trn 20210202 1330


restore process

restore database myfullbackup.bak norecovery

restore database mydiffbackup.diff norecovery

restore log mylogbackup1.trn stopat 1230 norecovery -- i need the data after the accidental update.

restore log mylogbackup2.trn 20210202 1300 norecovery --- i need this data too.

restore log mytaillog.trn 20210202 1330 recovery -- i need this data too.


if the restore process will only restore transactions up until (point in time) 1230 and the restore process doesn't alllow me to add anymore transactions. how do I get the rest of the transactions from the back up log transaction to be added to my database (myfullbackup) that occurred in mylogbackup2 and mylogbackup3? there is alot of data that is there but because point in time will only allow me to capture data up to this point. Is there a way to add the rest of the data that happened after the restore point in time. what happens to the rest of the data? do you just have to accept the fact that it can't be recovered? or do you have to recover it from a temp table and update the database manually? is this the only solution?

databasebackup-restore
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

Once you bring the database online after the point-in-time restore, you cannot then subsequently restore more transaction logs.

Either restore to the point-in-time, then query for the data you want to retain, store it somewhere outside of this database, then reapply it to the 'real' database or another fully restored copy.

Or, restore to the point-in-time, and use the WITH STANDBY option to allow you to read the database, query for the affected data, again storing it elsewhere, then continue the log restores. Finally reapply the data once the restore is complete

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.