Rollback DELETE!

Hi all,

I've just DELETED 19 records from a table just now in SQL Query Analyzer . How do I recover those records, or how do I roll back the transaction? What is the quickest way to recover those records?
LVL 4
DangerizAsked:
Who is Participating?
 
JimV_ATLConnect With a Mentor Commented:
SQL Server auto-commits each transation (i.e. your delete), unless you explictly use transaction blocks.

The way to get back your deleted records is to restore from the most recent full backup, then restore transaction log backups up until the most recent point in time prior to the data loss.  If your environment doesn't do this, then you'll want to set this up, so that next time you have this problem, you're covered.
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Also, it's always a good idea to backup a table, especially a critical table, before doing any types of deletes and updates.  Also, you should use an equivalent SELECT statement to refine the logic prior to issuing an UPDATE or DELETE.
0
 
ShivshankarConnect With a Mentor Commented:
As a precaution, always set
SET implicit transactions OFF

And then, always commit/rollback your transactions.

Rgds
====
Shiva
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Scott PletcherSenior DBACommented:
Actually, although it may not sound right, I think that should be:

SET IMPLICIT_TRANSACTIONS ON
GO

which forces a transaction to be used, forcing you to explicitly either COMMIT or ROLLBACK.

Of course, when done, use:

SET IMPLICIT_TRANSACTIONS OFF
GO

to return to normal autocommit mode.




0
 
xenon_jeCommented:
Soryy to hear this, but as I see you don't have backups, so those records are lost :(
0
 
ispalenyConnect With a Mentor Commented:
If you have recovery model other than simple, you can.
Look at http://www.lumigent.com.

GOOD LUCK !
0
 
ShivshankarCommented:
Yes, I forgot the underscore in my comment!

Thanks for pointing it out, however.

====
Shiva
0
 
ShivshankarCommented:
Scott,
Oops. I overlooked. It should've been ON instead of OFF. I believe this is what you wanted to say, right?
0
 
CleanupPingCommented:
Dangeriz:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
All Courses

From novice to tech pro — start learning today.