none
Recover Deleted Data Without having Recent Full Backups RRS feed

  • Question

  • Good Evening All,

    I'm having a Database And It contains one Transaction Table, Accidentally  I was Deleted from  table Rows. And I don't have Recent full backups on that Database. But i have .Mdf(Master DataBase file),LDF(Lod Database file ) files. If there is any Possibility to recover Deleted Data's. Any one give Suggestion on this...

    Best Regards,

    Tamil Selvan A

    Wednesday, September 11, 2019 5:17 PM

All replies

  • Hello Tamil,

    What is the recovery model of your database? When you say you have a mdf and ldf files, you're meaning that you have a cold copy of these files taken in a point of time ago?

    Regards,
    Rafael

    Wednesday, September 11, 2019 5:46 PM
  • You could try attaching the MDF and LDF files as a new database, then copy the data over. HTH
    Wednesday, September 11, 2019 5:46 PM
  • Hello Friend,

    If there is no Full or diff backup it will be complicated.
    But, maybe you have some shadow copy and can come back long enough to retrieve this table.

    If this answer has helped you, mark it helpful so that someone else with a question or similar problem can find an answer or help more easily. * Jefferson Clyton Pereira da Silva - [ MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]


    Wednesday, September 11, 2019 5:54 PM
  • Recovery model is full.

    after deletion of records the mdf and ldf file groups are taken.

    ldf is writes transaction logs Sequentially right there is any possibility to restore deleted table rows

    Wednesday, September 11, 2019 5:58 PM
  • Ok, 

    Do you have a "usable" full backup? 
    Since your database are in FULL recovery model, are you taking LOG backup from this database?
    If you have a "good" full backup, you can take a t-log backup and do a point-in-time recovery.

    If not, you can read the LDF file using a paid-tool like "ApexSQL log".

    Regards,
    Rafael

    Wednesday, September 11, 2019 6:09 PM
  • If there is any Possibility to recover Deleted Data's.

    Without a backup there is no reliable way to recover the data, but you can try https://www.sqlshack.com/recovering-data-from-the-sql-server-transaction-log/



    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, September 12, 2019 6:34 AM
  • Have you tried to restore log backup? Before that, don't forget to backup the current data in case there's any loss. 

    Sabrina 

     

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, September 12, 2019 7:06 AM
  • Read this article: https://social.technet.microsoft.com/wiki/contents/articles/51727.sql-server-how-to-recover-from-corrupt-sql-server-database-when-recovery-via-tsql-might-not-be-possible.aspx
    Friday, September 13, 2019 5:21 AM
  • recover-from-corrupt-sql-server-database
    But the database isn't corrupt, the OP simply delete data by mistake.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, September 13, 2019 5:34 AM
  • Hi TamilShyni

    if you never have full backup for this database then following link help to you

    https://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

    Friday, September 13, 2019 5:34 AM
  • You can search in the transaction log to find the information about the deleted rows. 

    Use Your_DB_Name
    Go
    SELECT 
     [Current LSN],    
     [Transaction ID],
         Operation,
         Context,
         AllocUnitName
        
    FROM 
        fn_dblog(NULL, NULL) 
    WHERE 
        Operation = 'LOP_DELETE_ROWS'

    Check the operation column. You will also get the transaction ID  which you can use to get the LSN of the operation. That means we need the exact LSN when the DELETE statement began its transaction.

    USE Your_DB_Name
    go
    SELECT
     [Current LSN],    
     Operation,
         [Transaction ID],
         [Begin Time],
         [Transaction Name],
         [Transaction SID]
    FROM
        fn_dblog(NULL, NULL)
    WHERE
        [Transaction ID] = 'Enter_your_ID'
    AND
        [Operation] = 'LOP_BEGIN_XACT'

    Run and share the output. 


    Friday, September 13, 2019 6:22 AM
  • Hi TamilShyni

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers or share your methods with us. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Regards,

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 17, 2019 6:46 AM