CODEWITHSUNDEEP
sqldatabasesql-server-2008database-designsql-server-2008-r2
Ap9_Jacka
Ap9_Jacka

Reputation: 21

Undo a SQL Query that is already been Executed

I am currently searching a query that can reform data back to its original form.

For example if I do the following query:

UPDATE Pupil
SET Telephone = '999'
WHERE Telephone = '0161'

I have done this query and realized I do not wish to change the telephone and want it as it was before. I understand using views and copying the same table to test query's is useful.

But I am wondering if there is actually a query to redo a update or delete query I have made.

Upvotes: 0

Views: 1821

Answers (3)

Kit Z. Fox
Kit Z. Fox

Reputation: 674

Since you are using SQL Server 2008 R2, you might be interested in Change Data Capture. It wouldn't be an easy crtl-z, but it could help.

Upvotes: 0

SazooCat
SazooCat

Reputation: 170

This assumes other data might have already contained 999 which you would want left as it was or you could just revert all 999 back to 0161 by inverting your original query values.

If you have full database logging on and did it during a transaction then it might be possible to rollback just that one transaction... Certainly if you restore an old backup you can rerun the transactional changes since the backup.

Otherwise you may have to restore a backup to a copy database, find the matching record, and update to the old value from that one... Restoring to the same server as for example MyDataBaseName_Old means you can join across the databases to get the old record. e.g.

update MyDatabaseName.dbo.Pupil
set p.Telephone = pold.Telephone
from MyDatabaseName.dbo.Pupil p
inner join MyDatabaseName_Old.dbo.Pupil pold on p.PupilID = pold.PupilID
where pold.Telephone = '0161'

Sorry I can't be more help. Hope it gives you some hints for what else you might want to search for.

Upvotes: 1

Alex
Alex

Reputation: 8937

You can do it only my means of your BackUps. Use Full BackUp both with Transaction log backup. Then you'll be able to restore your database state to the needed one. Otherwise there is no way to restore updated rows in SQL Server. I've heard that in Oracle there exists a feature to make a query to the state of database, in which it was some time ago. Hope that SQL server will follow them and develop such feature too.

Upvotes: 0

Related Questions