update

Question

update

sahana

SSCrazy

Points: 2530
More actions
May 18, 2004 at 12:14 pm

#152149

I have updated a column(first_name) in my table emp.Instead of updating few fields which meats some criteria i have update all the rows in the DB.Its just an update stmt and there is no explicit transaction mentioned.
I dont have a back for the table.So how can i rollback my update statement and get my old data?Any chance?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Unfortunately, you don't have a backup. This type of oops is why the transaction log should be backed up regularly.

I don't think you'll be able to get the data back. You might be able to buy Log Explorer from Lumigent, which will allow you to read the transaction log and see what all was changed. You could then manually make the changes back.

But I really think you are out of luck this time.

-SQLBill

Peter P SSCrazy Points: 2777 More actions · Answer 2

It may be possible, if your transaction logs have not been truncated, to perform a backup now and then perform a restore to a point in time.

To restore to a point in time

Execute the RESTORE DATABASE statement using the NORECOVERY clause.
Execute the RESTORE LOG statement to apply each transaction log backup, specifying:
- The name of the database to which the transaction log will be applied.
- The backup device from where the transaction log backup will be restored.
- The RECOVERY and STOPAT clauses. If the transaction log backup does not contain the requested time (for example, if the time specified is beyond the end of the time covered by the transaction log), a warning is generated and the database remains unrecovered.

Good Luck

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Peter,

To restore the logs, you have to have a full backup to apply them to. Sahana doesn't have any backup at all.

If a full backup were to be done now, then it will be of the database with the bad data.

-SQLBill

Peter P SSCrazy Points: 2777 More actions · Answer 4

I could be wrong about this as I've never attempted it, but if she performed a backup of the Transaction Logs, (assuming they have not been truncated) and then performed a full backup of the database, what would prevent her from restoring to a point in time?

Pete

gljjr SSChampion Points: 10080 More actions · Answer 5

I think in this case Lumigent's Log Explorer is the only way to get the data back. Since there is no full backup prior to this she can't simply do a transaction log backup now and then restore to a point in time. If however there is an old full backup then doing a transaction log backup now and then restoring the full backup and tran log to a point in time just might work.

So, first I would look to find out if there is a full database backup and if so perform a transaction log backup. Then as stated above, restore the database using the full backup and then the log backup to the specific point in time. I would do this to a new database so that you can make sure you get the data you want before doing it over the top of your current database!

GOOD LUCK!

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

SQLBill SSC Guru Points: 51440 More actions · Answer 6

Peter,

Transaction logs have to be applied to a full backup that was done BEFORE the transaction logs are backed up. This is why SQL Server advises that you backup your databases immediately after creating them. Then you always have a full backup to work with.

-SQLBill