?
Solved

Rollback DELETE!

Posted on 2003-03-18
9
Medium Priority
?
4,031 Views
Last Modified: 2010-08-05
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?
0
Comment
Question by:Dangeriz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 5

Accepted Solution

by:
JimV_ATL earned 40 total points
ID: 8159455
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 40 total points
ID: 8159626
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
 
LVL 1

Assisted Solution

by:Shivshankar
Shivshankar earned 40 total points
ID: 8159651
As a precaution, always set
SET implicit transactions OFF

And then, always commit/rollback your transactions.

Rgds
====
Shiva
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8159712
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
 
LVL 9

Expert Comment

by:xenon_je
ID: 8159872
Soryy to hear this, but as I see you don't have backups, so those records are lost :(
0
 
LVL 13

Assisted Solution

by:ispaleny
ispaleny earned 40 total points
ID: 8160667
If you have recovery model other than simple, you can.
Look at http://www.lumigent.com.

GOOD LUCK !
0
 
LVL 1

Expert Comment

by:Shivshankar
ID: 8165227
Yes, I forgot the underscore in my comment!

Thanks for pointing it out, however.

====
Shiva
0
 
LVL 1

Expert Comment

by:Shivshankar
ID: 8165237
Scott,
Oops. I overlooked. It should've been ON instead of OFF. I believe this is what you wanted to say, right?
0
 

Expert Comment

by:CleanupPing
ID: 9275754
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question