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 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?
Answer by Kev Riley ·
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