We have an test database configured with our web application. Due to internal web application error the entries were get updated in the database. Is it there any way to recover the updated records?

My problem in brief :

My web application form has five fields such as name, applicant number, date, d.o.b, address. The database also configured with same columns. Now due to internal web application's error the applicant's number has been duplicated and whenever the second number is generated (let me say the data with applicant number 12123 is placed in the database and due to duplication of 12123 the database rows get updated) it gets updated.. sadly we didn't have any backups(no backups at all).

Now we are in need to recover the data of updated data's from the database is it possible to recover updated records..

My database recovery model was preselected as full

Any third-party tools to recover the data? Or any other methods to recover the updated rows from Microsoft SQL Server 2008 R2?

  • 1
    The method is called backups ..... – marc_s May 6 '13 at 6:54
  • Do you have no backups at all, or no backups of the data that you want? Also, what is the recovery model of your database (visible in the "Options" tab of the database properties, or by querying sys.databases)? – Nathan Jolly May 6 '13 at 7:03
  • @NathanJolly i have checked the database now and it was preseted as full ,and we dont have no backups at all :( – BlueBerry - Vignesh4303 May 6 '13 at 7:10
  • 1
    Related question: How do I get back some deleted records? – Nick Chammas May 6 '13 at 7:51
  • 1
    No backups, no joy. Sorry. – Marian May 6 '13 at 9:34

If you've ever taken a backup, and your recovery model is BULK LOGGED or FULL, then you might have some luck. Unfortunately if never took a backup before you had your problem, then you are probably bang out of luck (as Martin Smith pointed out in his comments below).

You can check whether you've ever taken a backup by looking at:

SELECT db_name(database_id) as 'database', last_log_backup_lsn 
FROM sys.database_recovery_status

If the database you've lost data from has a NULL for last_log_backup_lsn, then the database is still in auto-truncate mode and your changes have possibly be lost forever.

If this is a server that you can take offline, I'd suggest shutting down the server and taking a copy or two of the .mdf/.ldf files first, just in case you can get something useful out of them.

There are third party tools like Apex SQL Log or Toad for SQL Server which will allow you to interrogate the .ldf files directly and hopefully see what happened to the data, plus they can usually interrogate transaction log backup files as well. Unfortunately they're not cheap, but you maybe able to get the functionality that you need from a trial version (I don't use either so I can't be sure about this).

If you find another tool that allows you to look at the transaction log backups, but not .ldf files, you should be able to:

  1. Take a full backup
  2. Take your first a transaction log backup (probably uncompressed to maximise the chance that tools will work with it)

Then use the backup from step 2.

Unfortunately, your mileage may vary, so I'd suggest doing what you can with trial versions of tools before spending lots of money on a tool that might not get your data back.

| improve this answer | |
  • 3
    If you've never taken a backup, and your recovery model is BULK LOGGED or FULL then actually your recovery model is to all intents and purposes the same as simple. See When is FULL Recovery not Really FULL Recovery – Martin Smith May 6 '13 at 8:30
  • @MartinSmith wow - thanks for the correction. I'll update my post accordingly. – Nathan Jolly May 6 '13 at 8:45

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.