1

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

0

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.

  • Unfortunately we are using the 108 tables that are on the primary, I tested it last month, when I had 3Tb of space to play with, but unfortunately that has all gone the journey now – Ste Bov Nov 29 '15 at 12:29
  • So you have 1TB of data in your primary file then? – Shawn Melton Nov 29 '15 at 12:31
  • We're using about 850 GB of it, its spilt into 9 files – Ste Bov Nov 29 '15 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.