?
Solved

Undo an Update Query

Posted on 2010-08-18
18
Medium Priority
?
490 Views
Last Modified: 2012-05-10
Hi Experts,
I have done this thing second time in my life. Ran an Update query without a Where clause!

I have no DB backup. What are my options?

Its SQL Server 2008.
0
Comment
Question by:CodeCruiser
  • 5
  • 4
  • 3
  • +4
18 Comments
 
LVL 31

Accepted Solution

by:
RiteshShah earned 336 total points
ID: 33463358
you can't do anything unless you have full backup or transaction log backup.
0
 
LVL 83

Author Comment

by:CodeCruiser
ID: 33463389
I have seen some articles showing the use of utilities like Log Explorer to undo selective queries from the log files but its not installing for me.
0
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 336 total points
ID: 33463422
If you put a begin tran before it you can rollback the transaction. If not, without backups you're a bit up the creek without a paddle I'm afraid. I've done it myself and you get that cold sweat moment.

Lee
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Assisted Solution

by:dhlotter
dhlotter earned 332 total points
ID: 33463430
hi,

try SQL Log Rescue (http://www.red-gate.com/products/SQL_Log_Rescue/)

it is free and can quite possibly do what you need to be done.
0
 
LVL 83

Author Comment

by:CodeCruiser
ID: 33463448
Tried the Log Rescue. Installs ok but never connects with always saying the server component not installed.
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 33463460
Log Rescue is for SQL 2000 only. Doesn't support 2005 or 2008 since Microsoft altered the log format.

Lee
0
 
LVL 2

Expert Comment

by:dhlotter
ID: 33463489
apologies, missed the part where you said SQL 2008
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 33463530
as I told you in very first post that without backups, you can't do anything.
0
 
LVL 83

Author Comment

by:CodeCruiser
ID: 33463541
Does it mean that there is no such tool as Log Explorer or Log Rescue which works with SQL Server 2008?
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 33463603
0
 
LVL 1

Expert Comment

by:inikolic
ID: 33463613
Or try APEX SQL Log viewer
0
 
LVL 1

Expert Comment

by:inikolic
ID: 33463617
0
 
LVL 3

Assisted Solution

by:PrakashRaoBS
PrakashRaoBS earned 332 total points
ID: 33463684
Try Apex SQL Log. Luminent Log Explorer has been acquired by Apex SQL now. So you can try the Apex SQL Log. It supports all SQL Server versions/editions including 2000, 2005, 2008, 2008 R2, x86, x64 and IA64.

http://www.apexsql.com/sql_tools_log.aspx
0
 
LVL 83

Author Comment

by:CodeCruiser
ID: 33463761
As I said earlier, I have been trying the Apex SQL Log. I tried all methods including installing client on client computer and server on server computer, installing both on client computer, and installing both on server computer but in all cases when i try to use it says server components are not installed properly.

One thing I noticed is that it ran *Uninstall*.sql when installing and also ran this when uninstalling so may be a problem with their installer!
0
 
LVL 1

Expert Comment

by:inikolic
ID: 33463883
You haven't mentioned you've tried Apex.

Anyway, what recovery model is set on your database?
(http://msdn.microsoft.com/en-us/library/ms189272.aspx)

If your recovery model is Simple then nothing is backed up in your transaction log.

If its full, try using methods described in:
http://searchsqlserver.techtarget.com/tip/How-to-restore-from-a-transaction-log-in-SQL-Server?ShortReg=1&mboxConv=searchSQLServer_RegActivate_Submit&

Btw, I'm in similar situation an hour ago I've dropped all UDFs from development machine instead
from deployment machine where I wanted to transfer those new I've been programming for last
two days.


Br
Ivan
0
 
LVL 1

Assisted Solution

by:inikolic
inikolic earned 332 total points
ID: 33464075
What I've discovered is, at least (and even with Simple recovery model on), that
you might find out what objects (and when) have been altered which might be
helpful with a damage assessment.

By executing custom reports on your SQL Server Logs.
Standard set of custom reports (*.RDL files) may be found at:
http://blogs.msdn.com/b/sqlrem/archive/2006/08/30/ssms-reports-3.aspx
direct link:  http://blogs.msdn.com/cfs-file.ashx/__key/CommunityServer-Components-PostAttachments/00-00-73-29-10/SSMS-Standard-Reports.zip

While some guidelines of how to create your own custom report may be found at:
http://blogs.msdn.com/b/sqlrem/archive/2006/11/20/custom-reports-in-management-studio.aspx


Good luck,
Ivan
0
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 332 total points
ID: 33464652
Did you try 'ApexSQL Recover' as well ?
http://www.apexsql.com/sql_tools_recover.aspx
http://www.apexsql.com/datasheets/New_Recover2008_Detailed.pdf

I am not sure about this tool. Just a google result.

Raj
0
 
LVL 83

Author Closing Comment

by:CodeCruiser
ID: 33499071
Had to do the data entry again. Thank you all for trying to help.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

840 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