March 30, 2011 at 5:51 am
Hi Experts,
I am facing a critical issue which is related to data recovery.
10:00 PM-- Database full Backup-- Completed
10:30 PM--- Tlog backup 1--completed
11:00 PM---by mistake User deleted 100 rows
11:30 PM--Tlog backup 2 ---completed
12:00 AM ---inserted 1000 rows using DTS package
12:15 Am --User reported to DBA for the cause of deletion
How to recover the data? Can you please provide the steps and scripts?
Thanks,
Paul
March 30, 2011 at 6:03 am
Restore the full backup as a new database. Restore WITH NORECOVERY
Restore the 10:30 transaction log backup WITH NORECOVERY
Restore the 11:30 log backup WITH STOPAT 10:59
If the database is still marked 'restoring' run
RESTORE DATABASE <dbname> WITH RECOVERY
Copy over those missing 100 rows
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
March 30, 2011 at 6:09 am
I would restore the database with a new name and stop immediately before the time of the deletion. Then, I would copy the rows deleted by accident to the original database and drop the restored copy of the database.
If you know the exact time of the delete, you can stop 1 minute before. If the exact time is unknown, I would restore with standby stopping some minutes before the estimated time of the accident, just to check if the rows are there. If you stop after the time of the accident, you will have to restart the restore from scratch.
Hope this helps
Gianluca
-- Gianluca Sartori
March 30, 2011 at 8:22 am
But, @12:00 AM ---inserted 1000 rows using DTS package. What about these records?
March 30, 2011 at 8:35 am
babuvalayil (3/30/2011)
But, @12:00 AM ---inserted 1000 rows using DTS package. What about these records?
What about them? Nothing I told you to do will affect those rows at 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
March 30, 2011 at 8:50 am
GilaMonster (3/30/2011)
Restore the full backup as a new database. Restore WITH NORECOVERYRestore the 10:30 transaction log backup WITH NORECOVERY
Restore the 11:30 log backup WITH STOPAT 10:59
If the database is still marked 'restoring' run
RESTORE DATABASE <dbname> WITH RECOVERY
Copy over those missing 100 rows
I think you may have missed the highlighted part of Gail's advice. You won't restore overwriting the original database, you will have to restore a new copy of the database from your restore media. When the new database is restored correctly, you can copy the rows to the original database and drop the restored copy.
-- Gianluca Sartori
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply