GUI Restore Problems on striped backups

Question

GUI Restore Problems on striped backups

Brandie Tarvin

SSC Guru

Points: 173108
More actions
August 21, 2015 at 5:41 am

#311009

So I'm testing some things in our new servers and was trying to restore a database from some striped backup sets. We have 4 files for our backups and restoring the FULL backups with no recovery worked beautifully via SSMS. But when I tried to restore the differentials (also striped across 4 files), the GUI gave me this error:
Unable to create restore plan due to break in the LSN chain.
I did some Google research to see how to locate when the break happened and I came across this article about how this is an SSMS 2012 bug.
So I tried the advice in this article to attempt a restore via Files and Filegroups, and ended up with the below error:
EDIT: Picture is attached if it is not showing in post.
This is getting annoying. I was able to restore via T-SQL, but I want to also be able to restore through the GUI. Does anyone have any work arounds for this problem?
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 6 posts - 1 through 6 (of 6 total)

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

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

Quick thought, script the restore rather than using same, it is straight forward and doesn't fail as the ui does. If you want I can dig up some of my automated restore script generation code.

😎

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 2

IIRC if you want to restore a database with a differential you would add the full AND differential files into the device window of the GUI. It has to see all the files up to the point you want to restore, so it will apply the full and then the differential (or log) backup.

I don't have an instance to test with but once I do will confirm.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 3

Shawn Melton (8/23/2015)
IIRC if you want to restore a database with a differential you would add the full AND differential files into the device window of the GUI. It has to see all the files up to the point you want to restore, so it will apply the full and then the differential (or log) backup.

Has this changed since SQL 2008? Because it doesn't work that way in the older versions.

But please see if you can test it. I'd love to hear what the results are.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 4

Well I can duplicate your issue when I restore the full, leaving the database as NO RECOVERY and then attempt to restore the differential backup.

So I recovered the database again, which I am just restoring a backup from local database I have to a new database name. I then went into the GUI and added all the files (2 striped for full, 2 striped for differential):

My timeline is now showing the FULL and DIFF backups. It restored with no errors. The equivalent T-SQL that it ran:

USE [master]

RESTORE DATABASE [TraceAnalysisRestored] FROM

DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\Backup\TraceAnalysis.bak',

DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\Backup\TraceAnalysis2.bak'

WITH FILE = 1,

MOVE N'TraceAnalysis' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\DATA\TraceAnalysisRestored.mdf',

MOVE N'TraceAnalysis_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\DATA\TraceAnalysisRestored_log.ldf',

NORECOVERY, NOUNLOAD, REPLACE, STATS = 5

RESTORE DATABASE [TraceAnalysisRestored] FROM

DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\Backup\TraceAnalysis_1.dif',

DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\Backup\TraceAnalysis_2.dif'

WITH FILE = 1, NOUNLOAD, STATS = 5

GO

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 5

Shawn, that is AWESOME. Thank you for checking that. I'll pass this along to our local team.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.