restoring database with stopatmark option

  • 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

  • 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

  • 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. 

  • Fine, Got it

    Thanx deena

  • You might want to look at the BOL for using the WITH STOPAT .

    -SQLBill

  • 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

  • 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

  • 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

  • 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'.

  • 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.

  • 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