How to Rollback SQL

Hello experts,

I made a huge mistake on a production server, ran an update without the WHERE clause.

UPDATE myTable
SET Field1 = 'SOmetext',
Field2 = 'sometext2'

Is there a way to rollback? I read something a while ago about transaction logs.

SOS!

Thanks

Ouch - but you're not the first!

There is a way to rollback as SQL statement but you would have needed to issue the BEGIN TRANSACTION command before issuing the update. Unless you're using a query tool that issues it behind the scenes (then you can issue ROLLBACK), - but if your running the commands directly then you can't rollback without having done so.

A quick note before I continue with a possible solution: it's a good habit (as you've found) to do updates/deletes like this:

BEGIN TRANSACTION

...<your update statement>

And then watching the "rows affected" to make sure it's what you expected. If yes, issue the COMMIT command otherwise ROLLBACK.

Anyway - here's the possible solution. Find out what the backup strategy is on the production database. For example, is it one full backup a night followed by hourly incremental backups - then you will be able to restore the full backup followed by the transaction log backups sequentially to the point before you issued the update statement. Of course, this will undo all other data changes up to the last transaction log restore.

|||

You cant perform a rollback, but hopefully you have your backup procedures in order so you can make a database restore. The transaction logs will be used in the restore procedure.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

|||

Thanks for the prompt responses.

I looked further and it show that there are Full backups done nightly, but when I do a restore I get an error"

Restore failed for Server '<ServerName>'. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Cannot open backup device '<GUID of the device>'. Operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Smo)

I can see a list of backups on the restore window and I tried using a different backup but still get the error. The database is on a different drive than the SQL installation but on the same system.

I also tried to copy the files to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data and detach and attach but same error. Any ideas on what I can do?

Thanks.

|||

I can see a list of backups on the restore window and I tried using a different backup but still get the error. The database is on a different drive than the SQL installation but on the same system.

I also tried to copy the files to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data and detach and attach but same error. Any ideas on what I can do?

What type is your backup file? If it's *.bak then you can only open it in Restore Wizard. And if it's *.mdf/*.ldf, you can only attach it. It's different.

Also, the error message says sql server cannot open the backup file, are you sure the backup file/or the hard disk is not a damaged one?

Hope my suggestion helps