none
"unable to create restore plan to break in the LSN chain" during exercise in book administering MS SQL Server 2012 databases

    Question

  • Hi,

    Bought the MS book  "Administering MS SQL Server 2012 databases" and currently working in chapter 7 and its about mirroring. In one exercise you have to create a mirrored database. The first step is to to back up the databases. But these already giving troubles.....I've done the exercise three times and i'm keep getting the following error:  "Unable to create restore plan to break in the LSN chain".

    These are the steps i'm taking:

    1. Copy database wizard and copy Adventureworks2012 to Adventuremirror (A).
    2. Set to full recovery mode (A).
    3. Create a backup  (A).
    4. copy to other server  (B).
    5. Restore with No Recovery (B).
    6. Create a transactional log backup (A).
    7. Try to restore transactinal backup to other server (B). But the a yellow error message appears on top of the window : "Unable to create restore plan to break in the LSN chain."

    It seems a bit odd as i'm following the steps of the book.

    Greetz,

    Hennie

    Friday, August 24, 2012 11:39 AM

Answers

  • Impossible to answer withour seeing exactly what is performed in the GUI. And, there could even be a bug in the GUI. When in doubt, script whatever the GUI does and investigate those scripts/TSQL. You would then be able to determine whether there is a bug in the GUI or an misunderstandng of how the GUI works...

    Tibor Karaszi, SQL Server MVP | web | blog

    Friday, August 24, 2012 2:18 PM
    Moderator
  • Hi Hennie,

    A continuous sequence of T-Log backups is tied by a ‘Log Chain’, which starts with a FULL backup. Now, unless we run anything explicitly that breaks the log-chain (Ex., running BACKUP log TRUNCATE_ONLY or by switching to SIMPLE recovery model), the existing chain remains intact. With the log chain intact, you can restore your database from any FULL database backup in the media set, followed by all subsequent T-Log backups to the point of failure.

    You could use the T-SQL sentences as example:

    RESTORE DATABASE AdventureWorks2012
       FROM DISK = 'D:\SQLServerBackups\AdventureWorks2012.bak'
       WITH NORECOVERY;
    RESTORE LOG AdventureWorks2012
       FROM DISK = 'D:\SQLServerBackups\AdventureWorks2012.trn'
       WITH RECOVERY;

    You can always restore a database by applying T-LOG backups in sequence, if there are NO gaps in the Log-Chain, irrespective of any intermediate FULL or DIFFRENTIAL backups.

    Restore (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms186858.aspx.

    TechNet Subscriber Support
    If you are
    TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.

    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Monday, August 27, 2012 9:44 AM
    Moderator

All replies

  • RESTORE HEADERONLY FROM DISK ='PATH\FILENAME.BAK'

    RESTORE HEADERONLY FROM DISK ='PATH\FILEAME.TRN'

    AND CHECK THE LSN NUMBER OF BOTH THE BACKUP SETS


    Ramesh Babu Vavilla MCTS,MSBI



    • Edited by vr.babu Friday, August 24, 2012 12:41 PM
    Friday, August 24, 2012 11:47 AM
  • You mean LSN number? How to?

    The last LSN of backup is the first LSN of the trn?

    Checkpoint LSN and databaseLSN is the same

    Friday, August 24, 2012 11:56 AM
  • It seems that using the basic backup and restore scripts from the book gives a better result than using the GUI. So what's the difference?

    Friday, August 24, 2012 12:27 PM
  • Impossible to answer withour seeing exactly what is performed in the GUI. And, there could even be a bug in the GUI. When in doubt, script whatever the GUI does and investigate those scripts/TSQL. You would then be able to determine whether there is a bug in the GUI or an misunderstandng of how the GUI works...

    Tibor Karaszi, SQL Server MVP | web | blog

    Friday, August 24, 2012 2:18 PM
    Moderator
  • Hi Hennie,

    A continuous sequence of T-Log backups is tied by a ‘Log Chain’, which starts with a FULL backup. Now, unless we run anything explicitly that breaks the log-chain (Ex., running BACKUP log TRUNCATE_ONLY or by switching to SIMPLE recovery model), the existing chain remains intact. With the log chain intact, you can restore your database from any FULL database backup in the media set, followed by all subsequent T-Log backups to the point of failure.

    You could use the T-SQL sentences as example:

    RESTORE DATABASE AdventureWorks2012
       FROM DISK = 'D:\SQLServerBackups\AdventureWorks2012.bak'
       WITH NORECOVERY;
    RESTORE LOG AdventureWorks2012
       FROM DISK = 'D:\SQLServerBackups\AdventureWorks2012.trn'
       WITH RECOVERY;

    You can always restore a database by applying T-LOG backups in sequence, if there are NO gaps in the Log-Chain, irrespective of any intermediate FULL or DIFFRENTIAL backups.

    Restore (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms186858.aspx.

    TechNet Subscriber Support
    If you are
    TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.

    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Monday, August 27, 2012 9:44 AM
    Moderator
  • Thnx all for your answers. I appreciate!

    Although I do understand that you need more info about the GUI backup and restore scenario, but I did not do anything specific during this scenario. Just plain backup and restore as described in my initial post.

    The T-SQL restore and backup worked just fine and no problem here.

    The lack of time prohibits me to investigate this in more detail, unfortunately.  Hope to come back in the near future with a answer..

    Greetz,

    Hennie

    Monday, August 27, 2012 10:32 AM
  • Hi Hennie,

    Any progress?

    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Thursday, September 06, 2012 4:42 PM
    Moderator
  • I was having a similar poblem with FULL and DIFFERENTIAL backup.

    The solution was to select both files together when restoring through the GUI.

    you still can restore from FU//-DIFFERENTIAL but as on GUI operation not two 

    Friday, April 12, 2013 12:19 AM