How to restore Transaction_Log File

Question

How to restore Transaction_Log File

nikhil.desai1

SSCommitted

Points: 1948
More actions
May 10, 2010 at 7:09 am

#138915

Hi,
I have one table name aircraft. It contains data as follows.
Aircrafttype Description noseats
737 Boeing 737-300 Jet 300
ATP Advanced Turbo Prop48
DC9 McDonnel Douglas Jet120
F24 Fokker-Friendship 48
S60 Shorts-360 36
I use following statement
Begin Tran
update aircraft
Set noseat = 50
where Aircrafttype = 'F24'
update aircraft
Set noseat = 38
where Aircrafttype = 'S60'
Commit Tran
After this statement the records get updated. After 2 min i come to know that the records update were wrong.
Now i want the original records i.e. I don't want updated record for this table. How can i get the original record.

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

Restore the database from a backup taken before the change. If you don't want to overwrite the entire database, restore the backup as a new database and copy the data across.

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

nikhil.desai1 SSCommitted Points: 1948 More actions · Answer 2

If we don;t have any backup taken than how to get the original data back.

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

You don't.

There's no way, from the native tools, to undo changes once committed. That's what a backup is for.

You could try a 3rd party log reader tool. They cost a fair bit though.

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

nikhil.desai1 (5/11/2010)
If we don;t have any backup taken than how to get the original data back.

If you are just looking for a way to be safe, and not actually trying to perform this - I would recommend the following:

BEGIN TRANSACTION;

SELECT *

INTO dbo.Aircraft_Backup_20100511

FROM dbo.Aircraft

WHERE AircraftType In ('F24', 'S60');

UPDATE ...

-- the rest of your code

Basically, back up the entries you are going to update before the change. Then, you have a way of getting back to the original entries if you need to rollback the changes. Of course, you need to remove the backup table some time later - but that can be done easily enough.

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