April 4, 2012 at 12:23 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.
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:26 pm
DBA_SQL (4/4/2012)
Oh ya, so now how can I get the deleted data, can you provide any script for this plz. Thank you
Really? Do you still have the original production database? What time did you take the tail log backup? What time did the user delete the data you want to recover? Do you know what data the user deleted? Do you have RedGate Data Compare?
April 4, 2012 at 2:13 pm
Here is the thing..it got back my deleted data...
-- I am just trying to learn..not for my office purpose..just interested to do things and learn by myself.
--Restore Files if DELETED
/*RESTORE HEADERONLY
FROM DISK = 'C:\Backups\xxxx.bak'; */
--To get last backup time information
RESTORE DATABASE TestDB2
FROM DISK = 'd:\backups\fulltestdb2.bak'
WITH NORECOVERY,STATS = 10,
STOPAT = '2012-04-04 14:00:39.000'
--diff db
restore database testdb2
from disk = 'd:\backups\Diff1.bak'
WITH NORECOVERY,STATS = 10,
STOPAT = '2012-04-04 14:00:39.000'
--Restoration of First log
restore log testdb2
from disk = 'd:\backups\log3.trn'
WITH NORECOVERY,STATS = 10,
STOPAT = '2012-04-04 14:00:39.000'
--Restore the tail-log backup (from backup set 3).
RESTORE LOG testdb2
FROM DISK = 'd:\backups\TailLogTESTDB2.bak'
WITH NORECOVERY,
STOPAT = '2012-04-04 14:00:39.000'
RESTORE LOG testdb2
FROM DISK = 'd:\backups\TailLog1TESTDB2.bak'
WITH NORECOVERY,
STOPAT = '2012-04-04 14:00:39.000'
restore log testdb2
from disk = 'd:\backups\log4.trn'
WITH norecovery,STATS = 10,
STOPAT = '2012-04-04 14:00:39.000'
RESTORE DATABASE Testdb2
WITH RECOVERY;
select * from Test1 order by 1
April 4, 2012 at 2:51 pm
Yes, that will get you back anything deleted after '2012-04-04 14:00:39.000'
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
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply