SQL Server Performance

MS SQL 2005 Data missing

Discussion in 'SQL Server 2005 General DBA Questions' started by michellesbs, Sep 22, 2010.

  1. michellesbs New Member

    Hi,
    I am using SQL 2005. Recently i found some of the data missing after few days entered.
    Some of the Master Table records missing but detail table still remain the record
    I suspect somebody access to the database and delete the records.
    I would like to know any way for me to trace back, how the data was missing?
  2. ashish287 New Member

    I have recently faced the similar problem to recover the data. So would like to share my exp with you to recover the data and hope it helps.
    If you have full and log backup, then restore them as new database. If you taking differential backup then, latest full --- latest differential and log backup which was taken just after latest full/differential.
    Now, while restoring the log, make sure you restore thwem with standby option, this will help you to keep restoring more logs and on the other hand you can analyse your restored data, either the data is there or not. So lets say after nth number of log restoration you found that data is missing then it mean data was there till (n-1)th log backup. It will give you surity that data was deleted between this perticular period.
    Then using the log reader tool you can read the transactions in the last log backup(nth log backup). It will show you the complete transaction and with almost all the details which will help you to find who deleted the data and when.
    Now to restore the lost data, I think you are well aware how to transfer the data between 2 different databases/tables.
    **** the more frequent you are taking log backup, the more easy it will be to find out the duration interval when the data get deleted.
    **** this approach is applicable when you have no audit on object/data delete. Else you can refer audit logs to find who deleted the data.
  3. satya Moderator

    Unless you have good backups it is not possbile to get the data back, similarly for future tracing you might monitor the connections on SQL Server or choose AUDIT tools.

Share This Page