How do I undo an update statement I made to a database

sql-server-2005 database-restore transaction-log
Haoest picture Haoest · Nov 22, 2010 · Viewed 7.4k times · Source

It's a test environment, I needed some data to test an Update query, but accidentally updated a column in all rows to have wrong data. Must I use a backup to restore the data back to the previous instance, or is there some secret with transaction log that I can take advantage of?

Thanks in advance.

Answer

Paul Sasik picture Paul Sasik · Nov 22, 2010

There is a non-secret transaction log called transaction log that you can recover from to a point in time. Here's how... That annoying little file with the ldf extension is the transaction log, as opposed to the .mdf file that is your normal db data.

Unless you have truncated the transaction log (ldf) or otherwise mucked with it, you should be able to do exactly the kind of restore (undo) that you're looking for.

Related questions

Recover deleted record in SQL Server

I accidentally deleted a row in a table and want to restore it. I've found a solution here: How to recover deleted records in MS SQL server I've tried to restore database from backup taken after delete. But I can't …

Read More
Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More