Restore a specific file group to a test environment without restoring the Primary file group (Error 3154) Scenario. 


I have a filegroup that we need to restore to get data back that has
been removed from the database after the last backup I cannot touch
The production systems because they are running
The primary filegroup is just over 1TB in size (don't blame me I didn't do that and it makes me want to cry)
I don't have anywhere large enough to restore that Primary filegroup
The filegroup I wish to restore is only 60 GB in size
I simply need the data from one table out of the filegroup
The system is all in a remote location so the backup can not be removed from there and potentially loaded elsewhere
I have a test 'copy' of the database, but that is generated through scripts and is 1/1000 of the size but contains all the same tables etc as the main database, just next to none of the data content
I only need to restore from the full backup


Code Sample

RESTORE DATABASE testEnvironment
filegroup = 'requiredFileGroup'
from disk = 
N'\\remotestorage\backupfile.bak'
with REPLACE,
move
N'requiredFileGroup_file1' to N'F:\requiredFileGroup_file1.ndf',
move
N'requiredFileGroup_file2' to N'F:\requiredFileGroup_file2.ndf',
stats = 1


Issues


When trying to restore I get the error 3154 


The backup set holds a backup of a database other than the existing 'testEnvironment' database.

This occurs when trying to restore to a new file location, or overwriting the files that currently exist in the test environment
As mentioned the Primary Filegroup is too large to be restored anywhere


Any ideas on a work around for this would be greatly appreciated

Ste
Well, sad to say you can't do a piecemeal restore and bring a secondary filegroup online without the primary being online first. If there is a hack to get around this I am not aware of it.

If your primary filegroup is at 1TB and you are not using it, why not just do a one time shrink to a more manageable size? This is not uncommon to do in production environments where a large amount of data may have been purged and you want to reclaim the space. As well, if you have no ability to test your backups your are in a far worse situation.