SQL Server Performance

ACCIDENTLY DELETED RECORDS

Discussion in 'ALL SQL SERVER QUESTIONS' started by SOUBIN S, Jan 16, 2012.

  1. SOUBIN S New Member

    Hi,
    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.
  2. Luis Martin Moderator

  3. davidfarr Member

    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.
  4. MichaelB Member

    Good answer:)

Share This Page