Restoring Filegroup

Question

Post reply

Restoring Filegroup

subhrajit46

SSCommitted

Points: 1595
More actions
May 6, 2010 at 3:55 am

#144485

Suppose I have a database with multiple file and filegroups. I have a full database backup.
Is it possible to restore a particular filegroup from the above backup?
If yes, how?
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ajitgadge Old Hand Points: 334 More actions · Answer 1

You can do that with Partial database restore operation. Check below link for more information and syntax.

http://msdn.microsoft.com/en-us/library/aa196613(SQL.80).aspx

subhrajit46 SSCommitted Points: 1595 More actions · Answer 2

I have tried partial restore but it is making other filegroups offline.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

Primary filegroup needs to be restored/available then only you can proceed with the rest ones.

http://msdn.microsoft.com/en-us/library/aa196613(SQL.80).aspx

Partial restore operations work with database filegroups. The primary filegroup is always restored, along with the files that you specify and their corresponding filegroups. The result is a subset of the database. Filegroups that are not restored are marked as offline and are not accessible.

subhrajit46 SSCommitted Points: 1595 More actions · Answer 4

Thanks Manu.

Yes, the PRIMARY filegroup will be restored before restoring the target filegroup. But it marks the other filegroups offline.

So, how to take these filegroups back online?

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

Partial restore just provides a mechanism to restore part of the database to another location so that the damaged or missing data can be copied back to the original database.

Unrestored filegroups are marked as offline and are not accessible.

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 6

subhrajit46 (5/7/2010)
Thanks Manu.
Yes, the PRIMARY filegroup will be restored before restoring the target filegroup. But it marks the other filegroups offline.
So, how to take these filegroups back online?

You need to restore the log tail from the origenal. At the moment you have GUID's which are out of sync so sql thinks thers missing data.

Not sure what help it will be but the process is detail on page 556 (Restoring Files and Filegroups) of Microsoft SQL Server 2008 Administrator Pocket Consultant (it work in 2005 as well).

The only thing i would say is it deals with backing up particular filegroups and then restoring them.

HTH

Adam Zacks-------------------------------------------Be Nice, Or Leave