Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Partial piecemeal restore error Expand / Collapse
Author
Message
Posted Friday, April 18, 2014 11:11 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 3, 2014 10:59 AM
Points: 79, Visits: 247
I'm running a filegroup backup and restore test in the lab and I've hit a wall in my experiments.

I have DatabaseA which I've created a Primary filegroup to hold relational data, and a FS filegroup that is holding filetables. My goal here is to get the primary filegroup up and running so that an application can still access the relational data, then restore any further filegroups afterwards. This is important for minimizing downtime. I'm also hoping to take primary and secondary filegroup backups at different times, since the relational data is what more frequently updated and is much smaller than the filetables.

DatabaseA and DatabaseA_Partial are on the same instance.

I've executed separate backups:

BACKUP DATABASE DatabaseA
FILEGROUP = 'PRIMARY'
TO DISK = 'D:\SQLBACKUP\Primary.bak'
GO

BACKUP DATABASE DatabaseA
FILEGROUP = 'FS'
TO DISK = 'D:\SQLBACKUP\FS.bak'
GO

Backups are successful

I started the restore with:

RESTORE DATABASE DatabaseA_Partial
FROM DISK = 'D:\SQLBACKUP\Primary.bak'
WITH PARTIAL,
RECOVERY,
MOVE 'DatabaseA' TO 'D:\SQLDATA\DatabaseA_Partial.mdf',
MOVE 'DatabaseA_log' TO 'L:\SQLLOGS\DatabaseA_Partial.ldf'
GO

Everything is good so far. The database is restored, and I can access the relational data.

I run the following query:

USE DatabaseA_Partial
GO
SELECT type_desc ,
name ,
physical_name ,
state_desc
FROM sys.database_files

And the output is:

type_desc	        name	                        physical_name	                               state_desc
ROWS DatabaseA D:\SQLDATA\DatabaseA_Partial.mdf ONLINE
LOG DatabaseA_log L:\SQLLOGS\DatabaseA_Partial.ldf ONLINE
FILESTREAM MEDIA_0 D:\SQLDATA\MEDIA_0t RECOVERY_PENDING
FILESTREAM MEDIA_1 F:\SQLDATA\MEDIA_1t RECOVERY_PENDING
FILESTREAM MEDIA_2 G:\SQLDATA\MEDIA_2t RECOVERY_PENDING
FILESTREAM MEDIA_3 D:\SQLDATA\MEDIA_3t RECOVERY_PENDING
FILESTREAM MEDIA_4 F:\SQLDATA\MEDIA_4t RECOVERY_PENDING
FILESTREAM MEDIA_5 G:\SQLDATA\MEDIA_5t RECOVERY_PENDING
FILESTREAM MEDIA_6 D:\SQLDATA\MEDIA_6t RECOVERY_PENDING
FILESTREAM MEDIA_7 F:\SQLDATA\MEDIA_7t RECOVERY_PENDING
FILESTREAM MEDIA_8 G:\SQLDATA\MEDIA_8t RECOVERY_PENDING
FILESTREAM MEDIA_9 D:\SQLDATA\MEDIA_9t RECOVERY_PENDING

Cool right?

Now I'd like to restore the FS filegroup

I've tried:

RESTORE DATABASE DatabaseA_Partial
FILEGROUP = 'FS'
FROM DISK = 'D:\SQLBACKUP\FS.bak'
WITH RECOVERY
GO

And I receive the following error:

Msg 3116, Level 16, State 2, Line 27
The supplied backup is not on the same recovery path as the database, and is ineligible for use for an online file restore.


I have read http://www.sqlservercentral.com/Forums/Topic412470-357-1.aspx and I don't think this is the issue I'm facing. I've also tried the above process after deleting DatabaseA and performing the restore without the 'MOVE' for DatabaseA

Any help or suggestions would be appreciated

Thanks


Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
Post #1563116
Posted Friday, April 18, 2014 11:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:01 PM
Points: 242, Visits: 447
I am not sure if this is strictly possible. The "Restrictions" section of the BoL page for Piecemeal Restores seems to say you can not restore logs after a piecemeal restore, if the initial restore does not include the FileStream data, but it does not seem to say much about your test case. Since you are not restoring logs, I doubt the CONTINUE_AFTER_ERROR directive is going to do much for you, but it is worth a 2 minute test.
Post #1563118
Posted Friday, April 18, 2014 12:00 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 3, 2014 10:59 AM
Points: 79, Visits: 247
Thanks Matt.

Yeah I'm a bit skeptical as to whether this can be done or not myself, but I was hoping someone with more experience would have good news for me :)

I executed:

RESTORE DATABASE DatabaseA
FILEGROUP = 'FS'
FROM DISK = 'D:\SQLBACKUP\FS.bak'
WITH RECOVERY,
CONTINUE_AFTER_ERROR
GO

And received the same error:

Msg 3116, Level 16, State 2, Line 1
The supplied backup is not on the same recovery path as the database, and is ineligible for use for an online file restore.


Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
Post #1563123
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse