June 14, 2006 at 9:19 am
hi,
while restoring a database log backup, i would like to use stopatmark option, what should i give for the 'mark_name' field for stopatmark option.
deena
June 14, 2006 at 11:09 am
What mark did you put in your transaction? STOPATMARK only works if you use marks in your transactions.
Refer to the BOL, use the index tab, enter 'marking transactions'.
-SQLBill
June 14, 2006 at 11:17 am
You need to use the name of the mark that was used to mark the transaction.
(ie. BEGIN TRANSACTION DailyDeleteOldData WITH MARK
DELETE FROM OrderHistory
COMMIT TRANSACTION DailyDeleteOldData)
As you see this transaction has been marked as 'DailyDeleteOldData' So for the exacmple my restore statement should look like this.
RESTORE LOG MyDatabase
FROM DISK=N'D:\LOGBACKUP\mydatabase.bak'
WITH FILE =2, STOPBEFOREMARK='DailyDeleteData', RECOVERY
I used stopbefore since I want to recover to a point before the delete happened. Stopatmark option will restore to right after the marked transaction completed.
You can select against the logmarkhistory table in msdb database to see what transaction marks have been made.
June 15, 2006 at 2:20 am
Fine, Got it
Thanx deena
June 15, 2006 at 6:08 am
You might want to look at the BOL for using the WITH STOPAT .
-SQLBill
June 17, 2006 at 8:01 am
hi,
Where could i get a detailed explanation of stopat option with mark_name, if the posted replies or not sufficient, I could not find such an example in BOL
thanx in advance
deena
June 18, 2006 at 10:26 am
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_9ttf.asp
The above article shows marked transaction basics and the article below is I think exactly from BOL but I hope it helps...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp
June 19, 2006 at 7:21 am
WITH STOPAT and WITH STOPATMARK are two different things.
WITH STOPATMARK requires you to have set a mark in your transaction.
WITH STOPAT just requires that you be restoring a LOG backups and you enter the time that you want the restore to stop at. For example, someone deletes something at 1005 AM. You would restore the database and all the logs using WITH STOPAT '2006-06-19 10:00 AM'.
-SQLBill
June 30, 2006 at 4:49 am
ok, fine
Now I have been trying to restore a transaction log using only stopat not stopatmark, after restore
the respective database in EM shows a (loading)
I could not access that database, EM says that database is in middle of restore.
However, all the transaction logs are restored with recovery,stopat='completion time of last log backup'.
June 30, 2006 at 9:41 am
It happened to me just 2 days ago. I had to restore to point in time using stopat prior to specific transaction. I restored the db but it stayed in loading status for a very long time. Then I tried restoring with the prior trans log backup before the one that I was trying to use. That operation completed in seconds and database was online ready to use. So try to restore with the trans log backup file prior to the one you're using and see if it will complete. I recommend restoring the db with a new name also.
July 1, 2006 at 5:37 am
ya, it works fine,
I have restored the database prior to a transaction log which makes the problem, then how it would be the point-in-time recovery.
regards deena
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply