Solved

Undo an Update Query

Posted on 2010-08-18
18
478 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 84 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 84 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 2

Assisted Solution

by:dhlotter
dhlotter earned 83 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 83 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 83 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 83 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

820 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