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

restoresql-server-2012

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

Best Answer

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.

Related Question