Recovering to a Marked Transaction

Question

Post reply

Recovering to a Marked Transaction

zi

SSCrazy

Points: 2387
More actions
January 12, 2013 at 2:14 am

#287477

Dear Experts
Kindly can any one explains to me in steps how to use the option of Recovering to a Marked Transaction
1-why it is for all database(s) in the instance not one db
2-I wrote a query "begin tran uu with mark ;" then I made some updates
when I wrote the same transaction again to put another mark this msg raised
"The WITH MARK option only applies to the first BEGIN TRAN WITH MARK statement. The option is ignored."
3- I took a full and log backup, then tryied to restore this backups from SSMS, but the mark didnt appear to me
in the restore to : distnation (where is it)
4- what does this means
"You can recover related databases only to a marked transaction, not to a specific point in time. "
Thanks lot

Viewing 15 posts - 1 through 14 (of 14 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Have you read the following plus the related links in the article?

http://msdn.microsoft.com/en-us/library/ms188623(v=sql.100).aspx

zi SSCrazy Points: 2387 More actions · Answer 2

-what about this message when I write the transaction mark for the second time

The WITH MARK option only applies to the first BEGIN TRAN WITH MARK statement. The option is ignored."

And where is it in the SSMS in the restore proccess

Thanks lot

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

zi (1/12/2013)
-what about this message when I write the transaction mark for the second time
The WITH MARK option only applies to the first BEGIN TRAN WITH MARK statement. The option is ignored."

Error message is pretty clear. The WITH MARK only applies to the first transaction (of a set of nested transactions), put it on a second and it's ignored

And where is it in the SSMS in the restore proccess

RESTORE LOG ... WITH STOPATMARK or RESTORE LOG ... WITH STOPBEFOREMARK

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

zi SSCrazy Points: 2387 More actions · Answer 4

Thanks for replying

kindly, I am confused about this topic

I made a tran mark , and I cant find it in the GUI SSMS to restor the log at this point

What should I do

can I make a mark then do some updates and then do another mark

Thanks lot

zi SSCrazy Points: 2387 More actions · Answer 5

zi

SSCrazy

Points: 2387

January 12, 2013 at 7:30 am

#1576569

any help

Thanks

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

zi (1/12/2013)
I made a tran mark , and I cant find it in the GUI SSMS to restor the log at this point
What should I do

Use T-SQL. I gave you the restore outline.

can I make a mark then do some updates and then do another mark

In two separate transactions, probably. In two nested transactions, no, the earlier error stated as much.

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

zi SSCrazy Points: 2387 More actions · Answer 7

Thanks for replying

Kindly find the attached file, why the Tasks -- restore -- Transaction log is not enabled

Thank you very much

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

Err... wild guess... simple recovery model?

No log backups?

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

zi SSCrazy Points: 2387 More actions · Answer 9

Thanks for replying

please find attached, I solved the gray tranaction log, but I found the mark dialog box is empty and I sure I have done it

I dont know why?

Thanks lot

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

Is the mark within the selected log backup? For that matter, have you selected a log backup to restore?

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

zi SSCrazy Points: 2387 More actions · Answer 11

I repeated steps

-Mark transaction

-backup transaction log

-restore full db with no recovery and close existing connection

- db -- tasks -- restore -- transaction log

then marked transaction -- emplty dialog box

any idea, thanks

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

GilaMonster (1/12/2013)
Use T-SQL. I gave you the restore outline.

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

zi SSCrazy Points: 2387 More actions · Answer 13

I made

begin tran pp with mark ;

SELECT * FROM msdb..logmarkhistory;

The result is nothing, 0 rowes affected, what does this means ?

Thanks lot

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

From Books Online:

logmarkhistory
Contains one row for each marked transaction that has been committed. This table is stored in the msdb database.

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