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

Question

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

Answer 1

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.