select * from aa
update aa set City='chennai',LastName='vinoth';
ID FirstName LastName City
29 Abcrdrr vinoth chennai
1 John vinoth chennai
2 Joe vinoth chennai
35 raja vinoth chennai
38 Johsdfgn vinoth chennai
I have wrongly updated Lastname, City columns in all the rows. Now I want to rollback to the old table rows.
Using SQL-Server 2008R2.
Unless you have some kind of history table & trigger in place, to retain old values at every change, or you made a copy of the table before you ran the update, you will need to use the last backup that was taken before that update. Restore it (as some temp database) and extract the data.
You do have a backup available, right?
BTW, next time you're doing an update, I suggest you set it up like this:
SELECT *
-- UPDATE t SET Column1 = x, Column2 = y
FROM MyTable AS t
WHERE ...
Run the SELECT first to see exactly which rows will be updated. If needed, adjust the WHERE clause to get the rows you want to target. Only then mark the sentence from the UPDATE till the end and execute it. Much safer then running a "blind" update, as you did, forgetting the WHERE clause (I assume that was the problem).
Even safer - do it on some test database first :).
Another approach would be to use fn_dblog to check within Transaction logs. That is quiet advance topic, so would refer to an excellent article How to read and interpret the SQL Server log - by Remus Rusanu
To avoid in future, you can always use Transactions
BEGIN TRAN
BEGIN TRY
update tbl set City='chennai',LastName='vinoth' from aa AS tbl;
-- if update is what you want then
COMMIT TRAN
END TRY
BEGIN CATCH
-- if NOT then
IF @@TRANCOUNT > 0
ROLLBACK
THROW
END CATCH
Edit: When writing complex T-SQL, you should use a TRY-CATCH block with TRANSACTION so you don't lock resources if an exception happens or the query gets cancelled. Otherwise, if the query is not complex, it is best to not use transactions.
Also, as Blaz mentioned, its always good to take backup of the database or just the table before doing any changes to the database.
