0

I updated a table in my SQL Server 2008 by accident, I was updating a table from another by copying cell by cell, but I have overwritten the original table. Is there a way that I can restore my table contents as it was?

animuson
  • 53,861
  • 28
  • 137
  • 147
sikas
  • 5,435
  • 28
  • 75
  • 120

5 Answers5

5

This tool says it can do it, a bit pricey though. There is a trial period, maybe that will be long enough to get your data back:

http://www.apexsql.com/sql_tools_log.asp?_kk=log%20explorer&_kt=49fb8026-ca7c-4c5e-bb06-99ee95393472&gclid=CPWP48T3i6ICFY1a2godtTS7UQ

Ron Savage
  • 10,923
  • 4
  • 26
  • 35
4

No. If you commited the transaction, the only way to restore the original table is by getting it from your latest backup.

Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
  • I heard that there is something in the SQL Server I guess it is called transaction log, which contains all the transactions I made, but I`m not sure about it!! – sikas Jun 06 '10 at 16:48
  • @sikas - Do you know what recovery model your database is in? – Martin Smith Jun 06 '10 at 17:26
  • @sikas - It wasn't pablo that made the comment! You can find out by following one of the approaches here http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/find-out-the-recovery-model-for-your-dat (though I think you would still be looking at third party tools to read the log unless you have ever taken a database backup) – Martin Smith Jun 08 '10 at 12:02
1

if you have only a backup then restore that to another database and move just the table over (this way no other tables will be affected). If you have also transaction log backups then you can also do a point in time restore

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
1

I've heard that there are some tools that might be able to undo certain actions in certain circumstances. Here's a link to an interesting article about how to minimize data loss in these circumstances and it includes links to some tools.

You might want to ask this question at ServerFault as well as some sysadmins might know better.

Hans Olsson
  • 54,199
  • 15
  • 94
  • 116
-1

Check this link. It shows how to restore values using transaction log

https://www.experts-exchange.com/articles/28199/How-to-recover-deleted-rows-in-SQL-Server.html

The article shows how we can recover deleted data if it happens accidently. We can recover deleted rows if we know the time when data is deleted. We can achieve this goal using LSN ( Log Sequence Numbers ). It shows an example of restoring deleted rows by checking the transaction log with LSN values. For updates use 'LOP_MODIFY_ROW'.

After deleting rows check the log using the following query

Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE Operation  = 'LOP_DELETE_ROWS'

Based on the transactionID and number of rows affected, filter the result set. Then use RESTORE DATABASE to restore the values from log.

NOTE: It is best to do this restoration as soon as you realize the accidental modification/deletion. The more the operations happening in a database there is high probability that the transaction log values might get overwritten.

Nisha
  • 1,379
  • 16
  • 28