Solved

Undo an Update Query

Posted on 2010-08-18
18
473 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

759 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now