Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Apr 15 '11 at 2:10

This question came from our site for professional and enthusiast programmers.

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
4  
@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

2 Answers 2

Not sure if this helps in your case but take a look at http://weblogs.sqlteam.com/mladenp/archive/2010/10/12/sql-server-ndash-undelete-a-table-and-restore-a-single.aspx

share|improve this answer
    
From the OP, it seems that they updated primary key values, which means that the old values were overwritten in the MDF file. The article deals with deleted or truncated data, where the data is still around, and hopefully hasn't been overwritten yet. Even if they had deleted (rather than updated) the data, they would have had to stop the app from working as soon as possible to avoid overwriting data in the MDF and I doubt that they would have done that. So, I agree with Sankar, I'm pretty sure that this article won't help. –  darin strait Apr 19 '12 at 13:57

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

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.