locked
Unable to See List of Marked Transactions on New Server RRS feed

  • Question

  • I am trying to do a TFS test restore to new server following the TFS 2010 Backup and Restore processes below verbatim (SQL 2008 SP1):

    Backup TFS http://msdn.microsoft.com/en-us/library/ms253070.aspx

    Restore to Different Server http://msdn.microsoft.com/en-us/library/ms252516.aspx#RestoreDB

    Restore Single Server Deployment to New Hardware http://msdn.microsoft.com/en-us/library/ff459215.aspx

    I can restore the database to the restoring state on a new server; however, when applying the transaction log backup I don't see any marked transactions to restore to.  I don't even get a list to choose from. 

    I can restore the database to the original server successfully and can see list of marked transactions to restore to.

    Is there anything that we could keep me from seeing the list of marked transactions on the new server?  Does this have anything to do with the fact that the marks are stored in the msdb.dbo.logmarkhistory database?

    Thanks in Advance,

    Michael

    Wednesday, September 1, 2010 6:34 PM

Answers

  • Hi Michael, the answer to your question is that in order to perform the restore through the GUI on the other server you WILL need to manually bring across those transaction marks because this is what the GUI uses (unfortunately) it doesnt (and it makes sense for performance reasons) scan through the transaction logs to obtain it's list.

    Perhaps in the future they might allow the option to manually enter a tran mark to stop at, which would solve your frustration completely.


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    • Marked as answer by MichaelSparks Thursday, March 3, 2011 5:12 AM
    Sunday, February 27, 2011 12:35 PM

All replies

  • Hi Michael,

    Were you using SQL Server 2008 R2 in the new server?

    If so, this should be a known issue. Marked Transactions are missing from the "Select Marked Transaction" dialog in SQL Server Management Studio 2008 R2. I have submitted a feedback at the Connect site:
    https://connect.microsoft.com/SQLServer/feedback/details/593713/marked-transactions-are-missing-from-the-select-marked-transaction-dialog-in-sql-server-management-studio-2008-r2

    Currently, please use the script statement to work around the issue. For example:

    USE master
    GO
    RESTORE DATABASE AdventureWorks2008R2
    FROM Disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AdventureWorks2008R2.bak'
    WITH FILE = 1, NORECOVERY;
    GO
    RESTORE LOG AdventureWorks2008R2
    FROM Disk='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\AdventureWorks2008R2.bak'
      WITH FILE = 2,
      RECOVERY, 
      STOPATMARK = 'ListPriceUpdate';
    

    If there is anything unclear, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Thursday, September 2, 2010 7:50 AM
  • I am not using SQL Server 2008 R2, so it doesn't really apply to this situation.  I am using SQL Server Standard 2008 SP1.

    As a troubleshooting measure, I inserted all the transaction marks from the msdb.dbo.logmarkhistory into the same table on the new server.  After doing that, I am now able to see the list of marks to restore to.

    Should I have to do that in order to restore to a mark on a new server?  The documentation for restoring the TFS databases doesn't mention anything about it.

    Restore to Different Server http://msdn.microsoft.com/en-us/library/ms252516.aspx#RestoreDB

    Restore Single Server Deployment to New Hardware http://msdn.microsoft.com/en-us/library/ff459215.aspx

    Thanks,

    Michael

    Thursday, September 2, 2010 2:25 PM
  • Hi Micheal,

    You shouldn't need to insert the marks manually into table msdb.dbo.logmarkhistory on the new server. The marks should be in the logs you backed up.  What error do you get when you execute this on the new server:

    RESTORE LOG [Tfs_Configuration]
    FROM Disk='\\BackupPath\Tfs_Configurtion.trn'
      WITH  RECOVERY,  STOPATMARK = 'TfsMark'
     

    where Tfs_Configuration is the name of your configuration database and Tfs_Configuration.trn is the name of the log backup file

    Thanks,

    Marwa


    Marwa
    Thursday, September 2, 2010 5:34 PM
  • OK...that worked.  But why am I not able to do this from the Management Console on the new server?  I can do it on the original server?

    What if I wanted to STOPAT at a specific mark and not just the first one?

    Thanks,

    Michael

    Thursday, September 2, 2010 7:28 PM
  • Hi Michael,

    Are you using the correct .trn file in the management console?

    You can provide After option to stop at a specific mark. If AFTER datetime is specified, recovery stops at the first mark having the specified name exactly at or after datetime.

     RESTORE LOG [Tfs_Configuration]
    FROM Disk='\\BackupPath\Tfs_Configurtion.trn'
      WITH  RECOVERY,  STOPATMARK = 'TfsMark' AFTER='DateTime' 
     

    Thanks,

    Marwa


    Marwa
    Thursday, September 2, 2010 8:47 PM
  • I now know that I can use the T-SQL statement to restore it and thank you for your for help with that.

    Yes.  I am using the correct .trn file otherwise I wouldn't have gotten it to work on the original server.

    However, I still have not gotten an answer to my orginal question. Why can I see the list of marked transactions when restoring on the original server but cannot see the list when restoring on a new server?  I shouldn't have to copy the contents of the msdb.dbo.logmarkhistory to the new server or do I?

    I am trying to document our TFS backup and restore process and this "should" be somewhat simple for anyone on our team. 

    Thanks,

    Michael

    Thursday, September 2, 2010 9:52 PM
  • Hi Michael,

    Back to your original post, I am using SQL Server Management Studio(SSMS) 2008 SP1 too.

    I can only see the issus in SQL Server Management Studio 2008 R2. So, I would like to check the version of the SSMS you are using. Could you please 1) open the SSMS, 2) click 'Help' > 'About', and then get and post the version of the SSMS here?

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Friday, September 3, 2010 1:45 AM
  • Jin,

    The SSMS version is the same for both the original server and the restore server:

    Version 10.0.2531.0

    Thanks,

    Michael

     

    Friday, September 3, 2010 2:10 PM
  • Hi Michael,

    We have the same problem on Windows 2008 R1: We cannot see the Marked transactions in SSMS when trying to restore TFS database on another server.

    Nice bug :(

    Regards.

    Carl

    Monday, December 13, 2010 5:05 PM
  • Thanks Carl!  Good to know.  I was begining to wonder if I was the only one trying to do a restore.

    I have read the documentation over and over and I haven't missed anything.

    This problem will not be good in a disaster recovery scenario.

    Have you had any more luck getting an answer to this?

    Thanks,

    Michael

    Wednesday, February 16, 2011 11:22 PM
  • I was doing the same excercise of restoring TFS database onto a different server and was not able to see marked transaction list while restoring log backup. After reading the above post , i wonder if restore to different server has to be performed only by script and not through SSMS.

    Has anyone else able to restore TFS backup with marked transaction onto different server using SSMS.

    version - Microsoft SQL Server 2008 (SP1) - 10.0.2531.0

     

     

    Thursday, February 24, 2011 7:25 PM
  • Hi Michael, the answer to your question is that in order to perform the restore through the GUI on the other server you WILL need to manually bring across those transaction marks because this is what the GUI uses (unfortunately) it doesnt (and it makes sense for performance reasons) scan through the transaction logs to obtain it's list.

    Perhaps in the future they might allow the option to manually enter a tran mark to stop at, which would solve your frustration completely.


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    • Marked as answer by MichaelSparks Thursday, March 3, 2011 5:12 AM
    Sunday, February 27, 2011 12:35 PM
  • Have also raised this Connect suggestion https://connect.microsoft.com/SQLServer/feedback/details/648084/it-is-not-possible-to-use-the-ssms-gui-to-restore-a-transaction-log-to-a-point-in-time-using-transaction-marks-if-backups-from-different-instance

    ..been meaning to do that for a while.

    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Sunday, February 27, 2011 12:52 PM
  • This was very helpful Mark!

    Although this is not good news, I hope this helps others as the TFS 2010 backup/restore documentation has no reference to this issue.

    Thank You,

    Michael

     

    Thursday, March 3, 2011 5:20 AM
  • Cool, glad to be of service.

     


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Thursday, March 3, 2011 11:27 AM