cannot restore to a specific transaction

Question

cannot restore to a specific transaction

winston Smith

SSCoach

Points: 19500
More actions
April 13, 2007 at 5:39 am

#118173
im just doing some study here, and practice on backups.
I done a full backup on the adventureworks db.
i made some :
- Update a table,
- drop a table,
- update another table
now i want to restore the database to the point just before the drop table statement.
so i do a tail log backup.
i restore the full backup, differential backup. i click on the menu entry for transaction log restore. i select the backup device for the log, but when i click on "select marked transaction" i cannot select an individual transaction to revert to. what am i doing wrong?
update another table

now i want to restore the database to the point just before the drop table statement.

********************************************************************************************************************************************************

Please check the SQL Server error log about the time at time you had drop table. Try to restore the database prior to that time using STOP AT clause.

From BOL:

This example restores a database to its state as of 12:00 A.M. on April 15, 1998, and shows a restore operation that involves multiple logs and multiple backup devices.

RESTORE DATABASE MyNwind   FROM MyNwind_1, MyNwind_2   WITH NORECOVERYRESTORE LOG MyNwind   FROM MyNwindLog1   WITH NORECOVERYRESTORE LOG MyNwind   FROM MyNwindLog2   WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'

Minaz Amin SSChampion Points: 11052 More actions · Answer 1

Please see the BOL " How to Restore to the point of failure". I think you should restore it with STOP AT clause.

Regards,

Minaz Amin

"More Green More Oxygen !! Plant a tree today"

winston Smith SSCoach Points: 19500 More actions · Answer 2

i already checked that out, but that just shows me how to back up to the last committed transaction in the log backup, and not one of the other previous committed transactions in the log. There are for example 4 commited transactions in the log backup, i want to restore only as far as the 2nd one.

winston Smith SSCoach Points: 19500 More actions · Answer 3

from doing a bit more research i see that i am not using marked transactions. how do i restore to a specific transaction in the transaction log backup?

Minaz Amin SSChampion Points: 11052 More actions · Answer 4

From your Post:

made some :

Update a table,
drop a table, ----------------------------> Note the time in SQL Server error log

winston Smith SSCoach Points: 19500 More actions · Answer 5

thanks for that! it seems that point in time restoration is the clostest thing i can get to what i want. it would be a nice feature to be able to view each transaction in the log and select which one you want to restore to! thanks for the help all!

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 6

Since you can only restore to a point in time you need some method of determining which transaction is your final point. You might want to check Lumigent LogExplorer or Red-Gate SQL Log Rescue. Either of these allow you to browse the transaction log. It might help you to get more granular on your recovery.

Otherwise, everyone else covered the best methods.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

winston Smith SSCoach Points: 19500 More actions · Answer 7

i had a brief look at lugiment a while ago, but il have a look again. never really done much with it. you would expect a feature like that to be built into the server! i may have to attempt to write an app to do just that....