December 7, 2012 at 7:44 am
I need to restore a database to a location that does not have enough space for the secondary data file. Is it possible to restore without the secondary data file and not leave the database in a recovery state? The link below suggests the answer is no. I just want a second opinion.
Thanks,
DK
December 7, 2012 at 7:53 am
No. You need to restore all the files of the database.
December 7, 2012 at 9:07 am
If that secondary file is in a separate filegroup and you have enterprise edition, then you can do a partial restore and just restore the primary filegroup. If that secondary file is part of the primary filegroup, it has to be restored.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2012 at 9:22 am
DKlein (12/7/2012)
I need to restore a database to a location that does not have enough space for the secondary data file. Is it possible to restore without the secondary data file and not leave the database in a recovery state? The link below suggests the answer is no. I just want a second opinion.Thanks,
DK
I've used the partial filegroup restore just as Gail suggests to restore a database without having to restore the largest table in the database saving me over 200 GB on downstream restores. But the filegroups have to already be in place for it to work.
One little trick I found that works is to rename the table that exists in the filegroup that isn't restored, then recreate an empty table with the same name. That allows any code referencing the table to still work even though the filegroup containing the table was not restored.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply