April 18, 2014 at 11:11 am
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
FILESTREAMMEDIA_0 D:\SQLDATA\MEDIA_0t RECOVERY_PENDING
FILESTREAMMEDIA_1 F:\SQLDATA\MEDIA_1t RECOVERY_PENDING
FILESTREAMMEDIA_2 G:\SQLDATA\MEDIA_2t RECOVERY_PENDING
FILESTREAMMEDIA_3 D:\SQLDATA\MEDIA_3t RECOVERY_PENDING
FILESTREAMMEDIA_4 F:\SQLDATA\MEDIA_4t RECOVERY_PENDING
FILESTREAMMEDIA_5 G:\SQLDATA\MEDIA_5t RECOVERY_PENDING
FILESTREAMMEDIA_6 D:\SQLDATA\MEDIA_6t RECOVERY_PENDING
FILESTREAMMEDIA_7 F:\SQLDATA\MEDIA_7t RECOVERY_PENDING
FILESTREAMMEDIA_8 G:\SQLDATA\MEDIA_8t RECOVERY_PENDING
FILESTREAMMEDIA_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
April 18, 2014 at 11:46 am
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.
April 18, 2014 at 12:00 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply