Sql-server – Is it there anyway to restore updated records in Microsoft SQL Server 2008 R2

recoveryrestoresql serversql-server-2008update

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?

Best Answer

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.

Related Question