How to Fix “Unable to create restore plan due to break in the LSN chain” When Using the Restore Wizard in...
up vote
0
down vote
favorite
This question is about regaining functionality of the SSMS Restore Database Wizard / GUI assistant. I would appreciate it if there are no responses along the lines of "don't use it - script it instead".
If I try to invoke Restore Database on a few of our databases, the Restore Database Wizard / Dialog reports "Unable to create restore plan due to break in the LSN chain". (As an aside, it does so in SSMS 2016 and SSMS 2012 - SSMS 2014 simply never responds.)
Link to screenshot
In each case, this occurred after a database was restored and I suspect a log file backup occurred before a full backup took place.
Since then each of our affected databases have had many weekly full backups (we are talking six months worth for one particular case) but the chain is still preventing the wizard from being used.
Some databases are on SQL Server 2008R2 SP3 and others on 2014 SP3.
Now I've analysed msdb.dbo.backupset (I am quite comfortable with how LSN chains work) and each full backup (for any given affected database) has an unbroken chain.
To keep our history in line with our retention policy we perform msdb.dbo.sp_delete_backuphistory on a weekly basis keeping a month's worth of history. This obviously leaves "orphaned" backup sets but must be a red herring as the vast majority of our databases have this and they have no issues.
The time period from when his issue was first detected has long since gone from msdb.dbo.backupset.
Are there any ideas of how SSMS calculates its results for the wizard? Is there another repository of backup / LSN chain information other than msdb.dbo.backupset that I should be checking?
Thanks.
John
sql-server ssms database-restore asked Nov 22 at 12:48
TiVo
11
add a comment |
up vote
0
down vote
favorite
This question is about regaining functionality of the SSMS Restore Database Wizard / GUI assistant. I would appreciate it if there are no responses along the lines of "don't use it - script it instead".
If I try to invoke Restore Database on a few of our databases, the Restore Database Wizard / Dialog reports "Unable to create restore plan due to break in the LSN chain". (As an aside, it does so in SSMS 2016 and SSMS 2012 - SSMS 2014 simply never responds.)
Link to screenshot
In each case, this occurred after a database was restored and I suspect a log file backup occurred before a full backup took place.
Since then each of our affected databases have had many weekly full backups (we are talking six months worth for one particular case) but the chain is still preventing the wizard from being used.
Some databases are on SQL Server 2008R2 SP3 and others on 2014 SP3.
Now I've analysed msdb.dbo.backupset (I am quite comfortable with how LSN chains work) and each full backup (for any given affected database) has an unbroken chain.
To keep our history in line with our retention policy we perform msdb.dbo.sp_delete_backuphistory on a weekly basis keeping a month's worth of history. This obviously leaves "orphaned" backup sets but must be a red herring as the vast majority of our databases have this and they have no issues.
The time period from when his issue was first detected has long since gone from msdb.dbo.backupset.
Are there any ideas of how SSMS calculates its results for the wizard? Is there another repository of backup / LSN chain information other than msdb.dbo.backupset that I should be checking?
Thanks.
John
sql-server ssms database-restore asked Nov 22 at 12:48
TiVo
11
1
You can hook up SQL Profiler and see what commands and queries it issues to figure out its results; that might help you along. Ultimately, though, you're asking why a particular closed-source GUI app does something interesting when you suspect it is in the wrong for doing that interesting thing, which is probably something better suited for Microsoft support than SO.
– Jeroen Mostert
Nov 22 at 13:00
What version of SSMS are you using? I ask because in the release notes for the latest (as of this writing) version has mention of fixing a bug that might cause what you're experiencing. feedback.azure.com/forums/908035-sql-server/suggestions/…
– Ben Thul
Nov 26 at 17:57
Hi Ben. I am using SSMS 2012, 2014 and 2016. From your link, I've just installed SSMS 18 preview 5 and it would appear to have fixed this issue (which would appear to be years old). At least I've got a workaround until it is officially released. Thanks for the info.
– TiVo
Nov 27 at 10:55
Ben if you add your suggestion as an answer, I can mark it as a solution.
– TiVo
Nov 27 at 11:03
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
This question is about regaining functionality of the SSMS Restore Database Wizard / GUI assistant. I would appreciate it if there are no responses along the lines of "don't use it - script it instead".
If I try to invoke Restore Database on a few of our databases, the Restore Database Wizard / Dialog reports "Unable to create restore plan due to break in the LSN chain". (As an aside, it does so in SSMS 2016 and SSMS 2012 - SSMS 2014 simply never responds.)
Link to screenshot
In each case, this occurred after a database was restored and I suspect a log file backup occurred before a full backup took place.
Since then each of our affected databases have had many weekly full backups (we are talking six months worth for one particular case) but the chain is still preventing the wizard from being used.
Some databases are on SQL Server 2008R2 SP3 and others on 2014 SP3.
Now I've analysed msdb.dbo.backupset (I am quite comfortable with how LSN chains work) and each full backup (for any given affected database) has an unbroken chain.
To keep our history in line with our retention policy we perform msdb.dbo.sp_delete_backuphistory on a weekly basis keeping a month's worth of history. This obviously leaves "orphaned" backup sets but must be a red herring as the vast majority of our databases have this and they have no issues.
The time period from when his issue was first detected has long since gone from msdb.dbo.backupset.
Are there any ideas of how SSMS calculates its results for the wizard? Is there another repository of backup / LSN chain information other than msdb.dbo.backupset that I should be checking?
Thanks.
John
sql-server ssms database-restore asked Nov 22 at 12:48
TiVo
11
This question is about regaining functionality of the SSMS Restore Database Wizard / GUI assistant. I would appreciate it if there are no responses along the lines of "don't use it - script it instead".
If I try to invoke Restore Database on a few of our databases, the Restore Database Wizard / Dialog reports "Unable to create restore plan due to break in the LSN chain". (As an aside, it does so in SSMS 2016 and SSMS 2012 - SSMS 2014 simply never responds.)
Link to screenshot
In each case, this occurred after a database was restored and I suspect a log file backup occurred before a full backup took place.
Since then each of our affected databases have had many weekly full backups (we are talking six months worth for one particular case) but the chain is still preventing the wizard from being used.
Some databases are on SQL Server 2008R2 SP3 and others on 2014 SP3.
Now I've analysed msdb.dbo.backupset (I am quite comfortable with how LSN chains work) and each full backup (for any given affected database) has an unbroken chain.
To keep our history in line with our retention policy we perform msdb.dbo.sp_delete_backuphistory on a weekly basis keeping a month's worth of history. This obviously leaves "orphaned" backup sets but must be a red herring as the vast majority of our databases have this and they have no issues.
The time period from when his issue was first detected has long since gone from msdb.dbo.backupset.
Are there any ideas of how SSMS calculates its results for the wizard? Is there another repository of backup / LSN chain information other than msdb.dbo.backupset that I should be checking?
Thanks.
John
sql-server ssms database-restore sql-server ssms database-restore asked Nov 22 at 12:48
TiVo
11
asked Nov 22 at 12:48
TiVo
11
asked Nov 22 at 12:48
TiVo
11
asked Nov 22 at 12:48
TiVo
11
asked Nov 22 at 12:48
TiVo
11
11
1
You can hook up SQL Profiler and see what commands and queries it issues to figure out its results; that might help you along. Ultimately, though, you're asking why a particular closed-source GUI app does something interesting when you suspect it is in the wrong for doing that interesting thing, which is probably something better suited for Microsoft support than SO.
– Jeroen Mostert
Nov 22 at 13:00
What version of SSMS are you using? I ask because in the release notes for the latest (as of this writing) version has mention of fixing a bug that might cause what you're experiencing. feedback.azure.com/forums/908035-sql-server/suggestions/…
– Ben Thul
Nov 26 at 17:57
Hi Ben. I am using SSMS 2012, 2014 and 2016. From your link, I've just installed SSMS 18 preview 5 and it would appear to have fixed this issue (which would appear to be years old). At least I've got a workaround until it is officially released. Thanks for the info.
– TiVo
Nov 27 at 10:55
Ben if you add your suggestion as an answer, I can mark it as a solution.
– TiVo
Nov 27 at 11:03
add a comment |
1
You can hook up SQL Profiler and see what commands and queries it issues to figure out its results; that might help you along. Ultimately, though, you're asking why a particular closed-source GUI app does something interesting when you suspect it is in the wrong for doing that interesting thing, which is probably something better suited for Microsoft support than SO.
– Jeroen Mostert
Nov 22 at 13:00
What version of SSMS are you using? I ask because in the release notes for the latest (as of this writing) version has mention of fixing a bug that might cause what you're experiencing. feedback.azure.com/forums/908035-sql-server/suggestions/…
– Ben Thul
Nov 26 at 17:57
Hi Ben. I am using SSMS 2012, 2014 and 2016. From your link, I've just installed SSMS 18 preview 5 and it would appear to have fixed this issue (which would appear to be years old). At least I've got a workaround until it is officially released. Thanks for the info.
– TiVo
Nov 27 at 10:55
Ben if you add your suggestion as an answer, I can mark it as a solution.
– TiVo
Nov 27 at 11:03
1
1
You can hook up SQL Profiler and see what commands and queries it issues to figure out its results; that might help you along. Ultimately, though, you're asking why a particular closed-source GUI app does something interesting when you suspect it is in the wrong for doing that interesting thing, which is probably something better suited for Microsoft support than SO.
– Jeroen Mostert
Nov 22 at 13:00
You can hook up SQL Profiler and see what commands and queries it issues to figure out its results; that might help you along. Ultimately, though, you're asking why a particular closed-source GUI app does something interesting when you suspect it is in the wrong for doing that interesting thing, which is probably something better suited for Microsoft support than SO.
– Jeroen Mostert
Nov 22 at 13:00
What version of SSMS are you using? I ask because in the release notes for the latest (as of this writing) version has mention of fixing a bug that might cause what you're experiencing. feedback.azure.com/forums/908035-sql-server/suggestions/…
– Ben Thul
Nov 26 at 17:57
What version of SSMS are you using? I ask because in the release notes for the latest (as of this writing) version has mention of fixing a bug that might cause what you're experiencing. feedback.azure.com/forums/908035-sql-server/suggestions/…
– Ben Thul
Nov 26 at 17:57
Hi Ben. I am using SSMS 2012, 2014 and 2016. From your link, I've just installed SSMS 18 preview 5 and it would appear to have fixed this issue (which would appear to be years old). At least I've got a workaround until it is officially released. Thanks for the info.
– TiVo
Nov 27 at 10:55
Hi Ben. I am using SSMS 2012, 2014 and 2016. From your link, I've just installed SSMS 18 preview 5 and it would appear to have fixed this issue (which would appear to be years old). At least I've got a workaround until it is officially released. Thanks for the info.
– TiVo
Nov 27 at 10:55
Ben if you add your suggestion as an answer, I can mark it as a solution.
– TiVo
Nov 27 at 11:03
Ben if you add your suggestion as an answer, I can mark it as a solution.
– TiVo
Nov 27 at 11:03
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53431407%2fhow-to-fix-unable-to-create-restore-plan-due-to-break-in-the-lsn-chain-when-us%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
You can hook up SQL Profiler and see what commands and queries it issues to figure out its results; that might help you along. Ultimately, though, you're asking why a particular closed-source GUI app does something interesting when you suspect it is in the wrong for doing that interesting thing, which is probably something better suited for Microsoft support than SO.
– Jeroen Mostert
Nov 22 at 13:00
What version of SSMS are you using? I ask because in the release notes for the latest (as of this writing) version has mention of fixing a bug that might cause what you're experiencing. feedback.azure.com/forums/908035-sql-server/suggestions/…
– Ben Thul
Nov 26 at 17:57
Hi Ben. I am using SSMS 2012, 2014 and 2016. From your link, I've just installed SSMS 18 preview 5 and it would appear to have fixed this issue (which would appear to be years old). At least I've got a workaround until it is officially released. Thanks for the info.
– TiVo
Nov 27 at 10:55
Ben if you add your suggestion as an answer, I can mark it as a solution.
– TiVo
Nov 27 at 11:03