April 3, 2012 at 11:58 am
I would like to know how can we get the point in time recovery data information.
For ex: 12:00 AM - FULL BKUP
12:30 AM - TX LOG1
1:00 AM - TX LOG2
:
:
:
3:00 AM - DIFF BKUP1
3:30 AM - TX LOG6
3:50 AM - DISASTER TOOK PLACE
So, I know to recover the data till the time 3:30 Am:
Full BKUP + DIFF BKUP1 + TX LOG6
So, how can I get the data which I missed for last 20 mins i.e between(3:30 - 3:50).
Do we have any point in time recovery script which will be helpful to recover the information.
Please, do help me. Tried to google, but didn't find good information.
April 3, 2012 at 12:02 pm
DBA_SQL (4/3/2012)
I would like to know how can we get the point in time recovery data information.For ex: 12:00 AM - FULL BKUP
12:30 AM - TX LOG1
1:00 AM - TX LOG2
:
:
:
3:00 AM - DIFF BKUP1
3:30 AM - TX LOG6
3:50 AM - DISASTER TOOK PLACE
So, I know to recover the data till the time 3:30 Am:
Full BKUP + DIFF BKUP1 + TX LOG6
So, how can I get the data which I missed for last 20 mins i.e between(3:30 - 3:50).
Do we have any point in time recovery script which will be helpful to recover the information.
Please, do help me. Tried to google, but didn't find good information.
Depends on the disaster. If you lost the mdf/ndf file(s) but still had access to the ldf (t-log) file, you could complete a tail log backup and recover the data up to the point of failure. However, if you lost the ldf, you are out of luck.
April 3, 2012 at 12:20 pm
Ok..lets consider I do have mdf and recent ldf files before disaster then how can i get back the data of the time where I lost
April 3, 2012 at 12:27 pm
As Lynn said:
If you lost the mdf/ndf file(s) but still had access to the ldf (t-log) file, you could complete a tail log backup and recover the data up to the point of failure.
Same applies if you still had the mdf (though not sure what disaster would leave both files intact)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2012 at 12:59 pm
GilaMonster (4/3/2012)
As Lynn said:If you lost the mdf/ndf file(s) but still had access to the ldf (t-log) file, you could complete a tail log backup and recover the data up to the point of failure.
Same applies if you still had the mdf (though not sure what disaster would leave both files intact)
Only thing I can think of would be a power failure and the UPS systems failed (or ran out of juice) and the servers went down. Of course, if that happened, the servers should recover on their own when power is restored.
You're right, I can't think of any either.
April 3, 2012 at 1:18 pm
My question is how can we get the lost data between that time (20mins) interval? Do we have any script for that?
April 3, 2012 at 1:20 pm
DBA_SQL (4/3/2012)
My question is how can we get the lost data between that time (20mins) interval? Do we have any script for that?
Define your disaster. We have told you, that if you have access to the server (it isn't down) and the ldf (transaction log for the database) you can do a tail log backup.
April 3, 2012 at 1:56 pm
The data has got deleted with in the time log as mentioned earlier (20 mins) interval. TX log has not backed up. So, how can we get back the deleted data.
April 3, 2012 at 2:00 pm
DBA_SQL (4/3/2012)
The data has got deleted with in the time log as mentioned earlier (20 mins) interval. TX log has not backed up. So, how can we get back the deleted data.
To paraphrase. A user deleted data that should not have been deleted.
You know approximately when this occurred, correct?
Take a t-log backup, restore the last full backup to another server, then load all t-log backups using the STOPAT clause on the last t-log to recover the database up to the point in time that the user deleted the data. You can then restore the deleted data from this database to the production database.
Does this help?
April 3, 2012 at 2:14 pm
Lynn Pettis (4/3/2012)
Take a t-log backup, restore the last full backup to another server, then load all t-log backups using the STOPAT clause on the last t-log to recover the database up to the point in time that the user deleted the data. You can then restore the deleted data from this database to the production database.
Or just wait for the next scheduled transaction log backup, and then restore as Lynn described. I wouldn't personally take an ad-hoc log backup in that situation unless it was absolutely critical that the deleted data be restored immediately
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2012 at 2:18 pm
GilaMonster (4/3/2012)
Lynn Pettis (4/3/2012)
Take a t-log backup, restore the last full backup to another server, then load all t-log backups using the STOPAT clause on the last t-log to recover the database up to the point in time that the user deleted the data. You can then restore the deleted data from this database to the production database.Or just wait for the next scheduled transaction log backup, and then restore as Lynn described. I wouldn't personally take an ad-hoc log backup in that situation unless it was absolutely critical that the deleted data be restored immediately
I will agree, especially if the restore of the full backup is expected to take longer than the time until the next scheduled t-log backup.
April 4, 2012 at 7:30 am
Thank you Linn and Gylla..excellent answers..it helps me.
April 4, 2012 at 10:09 am
This is how I did and it worked out..
USE master
--Create tail-log backup.
BACKUP LOG TestDB2
TO DISK = 'd:\backups\TailLogTESTDB2.bak'
WITH NORECOVERY;
--FULL DB
RESTORE DATABASE TestDB2
FROM DISK = 'd:\backups\fulltestdb2.bak'
WITH NORECOVERY,STATS = 10
--diff DB
restore database testdb2
from disk = 'd:\backups\Diff1.bak'
WITH NORECOVERY,STATS = 10
--Recent TX LOG after diff
restore log testdb2
from disk = 'd:\backups\log3.trn'
WITH NORECOVERY,STATS = 10
--STOPAT = '2012-04-04 10:20:49.000';
restore log testdb2
from disk = 'd:\backups\log4.trn'
WITH noRECOVERY,STATS = 10
--stopat = '2012-04-04 10:39:39.000'
--Restore the tail-log backup
RESTORE LOG testdb2
FROM DISK = 'd:\backups\TailLogTESTDB2.bak'
WITH NORECOVERY;
RESTORE DATABASE Testdb2
WITH RECOVERY;
April 4, 2012 at 10:46 am
That's not going to do what you want.
That's the set of backup and restore statements you use if the data file has been damaged or lost. Not what you'd use for deleted data. In fact, all you'll achieve with that set is to make the DB unavailable for the duration of the restore and you'll end up with the DB in exactly the same state after the restore as it was before you started the restore, missing data and all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2012 at 12:11 pm
Oh ya, so now how can I get the deleted data, can you provide any script for this plz. Thank you
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply