SQL Server Performance Forum – Threads Archive


I have accidenly deleted some important records from one table.Can you please provide any suggession to recover those records, as those are very important for us.
The things I have is,
backup: I have only two months old backup
Trnasaction log: Not yet taken
Recovery Model: Full
Please provide a solution to reteieve those records.
Thanks in Advance.
There may yet be an easy solution here;
If your database has always been in Full Recovery mode, ever since your last full backup 2 months ago, and if you have made no other backup since that time, then do the following: Make a note of the date and approximate time that you think the important records were deleted. This is imporant for the final step later. Do a transaction log backup now of your current database, the database where the records were deleted from. Then restore the database starting with your full backup file from 2 months ago. You can do this from the SQL Server Management Studio.
Do not restore it over your current database, restore it as a new database under a new name (perhaps ‘MyDatabase’ as an example). Select the NORECOVERY option for this first restore operation. Then restore your transaction log backup that you took in the 1st step above. You should be able to restore the log up until the moment just before the records were deleted, if you know what that time was.
You will need to use the STOPAT option during your restore of the log file. The SQL syntax that you use will depend on your backup file name, database name, backup file path, etc.
An example below will hopefully point you in the right direction. RESTORE LOG [MyDatabase] FROM DISK = N’c:\backup\MyDatabase_backup_2012_01_18_173002_0764422.trn’ WITH FILE = 1, RECOVERY, STOPAT = ‘Jan 5, 2012 12:00 AM’; –the date and time before the records deleted. If these steps succeed then you should now be able to access MyDatabase, which will be a copy of your live database, in the state that it was just before the records were deleted.
You can then access these records and copy or re-import them back into your live database.
Best of luck.
Good answer:)

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |