I am in serious trouble and I am seeking professional advices here.

We are using MSSQL server 2008. We removed primary key, replaced exiting data with new data resulted losing our critical business data in its child tables on MSSQL Server. It was completely human mistake and we didn't have disk failure.

1) The last backup file was a month ago which means it is useless.

2) We created Maintenance Plans to backup our database at 12AM everyday but those files are nohwere to be found

3) A friend of mine said we can recover from Transaction Logs. When I go to Task>Restore> Transaction log is dimmed/disabled.

4) I checked Management>Maintenance Plans. I can't find any restored point there. It seems that our maintenance plan hasn't been working.

Is there any third party tool to recover lost/overwritten data from MSSQL table? Thanks a lot.

  • 2
    What does this return for your database? select d.name,recovery_model_desc,last_log_backup_lsn from sys.database_recovery_status r join sys.databases d on r.database_id=d.database_id – Martin Smith Apr 14 '11 at 11:17
  • 2
    You need a full backup and EVERY transaction Log since the Full backup (assuming you have no differential backups and assuming Full recovery model) – Mitch Wheat Apr 14 '11 at 11:20
  • 5
    @TeTe: why on earth didn't someone take a full backup before messing with schema and data? – Mitch Wheat Apr 14 '11 at 11:21
  • I think really the only hope you have left is if by some miracle the server that the database sits on has been backed up and restore the entire server. – JStead Apr 14 '11 at 11:26
  • Thanks Martin. I will try it when i get to work tomorrow morning. We got into serious trouble when our DBA was away on holiday. He did all the backup stuff and we completely neglected this whole backup thing. – TeTe Apr 14 '11 at 11:38

If your database is in full recovery mode and transaction chain is not broken (you have backup + transaction log since last full backup) then it might be possible to recover some or even all data by analyzing transaction log.

If transaction log is in full recovery mode then transactions are logged and can be read from t-log but unfortunately this is not an easy task.

Your options are to try and read this yourself using DBCC LOG command or third party tools such as ApexSQL Log or Quest Toad

  • Sadly there is no transaction chain as the db is not in full recovery mode but in simple. Read the comments on the question. -1 – TomTom Jun 13 '13 at 19:17
  • Yeah, I just saw the comments. Sorry about that. – Stanley Norman Jun 13 '13 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy