Revert Last Delete Statement on in SQL Mgmt Studio

Question

Revert Last Delete Statement on in SQL Mgmt Studio

guerillaunit

SSCarpal Tunnel

Points: 4249
More actions
July 5, 2012 at 9:04 am

#271985

Hi, I accidently processed a delete statement in mgmt studio that removed a couple of lines of data. What's the most efficient way for me to reverse this?

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the only way is to restore from backup.

if you were not inside an explicit transaction you created, the change would be committed immediately, with no way to reverse it.

if you have an available backup that you know contains the data, you'd typically restore as a different database name, and then insert into the table from the recently resotred database.

sorry, there is no easy fix for this.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

guerillaunit SSCarpal Tunnel Points: 4249 More actions · Answer 2

Thanks Lowell. Is there anyway to find where backups are being saved to? I took over a server cluster so I am not sure what network location the back up files are being saved in

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Check the SQL jobs for the one that runs the backups.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

guerillaunit SSCarpal Tunnel Points: 4249 More actions · Answer 4

I found the job that does the nightly backup. It is using a SSIS package; although I am not sure where this SSIS package is saved. How do I find where this SSIS package is saved?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Run this code and replace 'YourDBNameHere' with the name of the database you are working with

DECLARE @DBName varchar(128)

SET @DBName = 'YourDBNameHere'

Select a.database_name,a.backup_start_date

,BackupPath = b.physical_device_name

,BackupSizeMB = a.backup_size/1024/1024

,CompressedBackMB = a.compressed_backup_size/1024/1024

From msdb.dbo.backupset a

INNER JOIN msdb.dbo.backupmediafamily b

ON a.media_set_id = b.media_set_id

Where a.type = 'D'

And a.backup_start_date > GETDATE()-7

And b.physical_device_name not like '{%'

AND a.database_name = @DBName

Order By a.database_name,a.backup_start_date

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323518 More actions · Answer 6

another longshot: if CDC is enabled for your table, you could get the values fromt hat:

--find any tables that are tracked via Change Data Capture

SELECT

name AS TableName,

is_tracked_by_cdc

FROM sys.tables

WHERE is_tracked_by_cdc = 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dan-572483 SSCoach Points: 15870 More actions · Answer 7

When writing UPDATE or DELETE statements in Management Studio, always have a BEGIN TRAN in front of it.

This has saved my butt more than once, on occasions I expected to see "2 rows affected" but I saw "20,000 rows affected." Don't forget to run COMMIT TRAN or ROLLBACK TRAN afterwards (depending if you want to keep the change or not) or you may leave locked records that will stall end users applications.

Another thing I do is in my SQL scripts which contain various statements used for troubleshooting and back-end fixes, I put comment marks /* delete..... */ around update & delete statments so I have to specifically highlight them when I want to run them.

GSquared SSC Guru Points: 260824 More actions · Answer 8

Whenever I have to modify data in production, I start with "begin transaction" and end with "rollback", run the script, use OUTPUT to check the results, and, when I'm finally happy with the results, then and only then change "rollback" to "commit", and re-run.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

guerillaunit (7/5/2012)
Thanks Lowell. Is there anyway to find where backups are being saved to? [font="Arial Black"]I took over a server cluster[/font] so I am not sure what network location the back up files are being saved in

Shifting gears from the original problem...

You say "I took over a server cluster" and that implies that you're the acting DBA. You're questions are pretty basic for someone who "took over" and I recommend you get some training before you get into real trouble. This isn't a criticism... it's survival advice.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 10

Jeff Moden (7/5/2012)
guerillaunit (7/5/2012)
Thanks Lowell. Is there anyway to find where backups are being saved to? [font="Arial Black"]I took over a server cluster[/font] so I am not sure what network location the back up files are being saved in
Shifting gears from the original problem...
You say "I took over a server cluster" and that implies that you're the acting DBA. You're questions are pretty basic for someone who "took over" and I recommend you get some training before you get into real trouble. This isn't a criticism... it's survival advice.

+1

Ravi SQL SSCommitted Points: 1714 More actions · Answer 11

If you have a transaction log backup then you can restore to the point of time when the DELETE statement was executed or upto log sequence number (LSN) to recover the deleted data.

Regards,
Ravi.

guerillaunit SSCarpal Tunnel Points: 4249 More actions · Answer 12

Thanks G2 and Dan, I like the BEGIN and ROLLBACK statement combination.

Jeff, this seems like a particularly painful subject for you. I hope someday you'll get over the scars that have had such a significant impact on you

Jared SSC Guru Points: 61793 More actions · Answer 13

guerillaunit (7/5/2012)
Thanks G2 and Dan, I like the BEGIN and ROLLBACK statement combination.
Jeff, this seems like a particularly painful subject for you. I hope someday you'll get over the scars that have had such a significant impact on you

Huh? Not to be rude, but you clearly don't have the knowledge to be a functioning DBA if you don't understand that when you delete something it is gone. Jeff is trying to tell you that the best thing for your company and your job is to get proper training. So don't be a jerk to him when he is only trying to help you keep your job.

Jared
CE - Microsoft

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 14

GSquared (7/5/2012)
Whenever I have to modify data in production, I start with "begin transaction" and end with "rollback", run the script, use OUTPUT to check the results, and, when I'm finally happy with the results, then and only then change "rollback" to "commit", and re-run.

For particularly sensitive modifications - I will also create a backup table of either the whole table or the affected rows giving me the opportunity to recover/rollback the changes if necessary. This can be done using the OUTPUT statement - which is what I believe you are referring to.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs