Restore filegroup to database on different server

I am using SQL 2005 on windows server 2003.  I have 2 identical databases (aside from data and name) on 2 different servers.  I have 2 filegroups - Primary and AppSupport.  I am attempting to take the AppSupport filegroup, which only contains one file, and restore it from the DB on server A to the DB on server B.  Our reason for doing this is to determine if we can send our clients only the filegroup needed for upgrades and not touch the other filegroup containing their data.
I have attempted to take the database offline and replace that filegroup, but it wont come back online.  I have also attempted to backup and restore the other filegroup and transaction log along with the new filegroup but no luck there either.  I keep getting errors about rolling forward to an LSN.  I have tried so many different options and methods I lost track of what I did.
My Questions is: Is this even possible, and if so, what is the process?  I have attached my last failed attempt for reference.
/*
RESTORE DATABASE [File_TEST] FROM  DISK = N'D:\MSSQL.1\MSSQL\Backup\File_TEST_05022009.bak' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO
*/
 
alter database File_test set recovery full
go
BACKUP DATABASE [File_TEST] 
FILEGROUP = N'PRIMARY' TO  DISK = N'D:\MSSQL.1\MSSQL\Backup\File_TEST_PRIMARY_05022009.bak' 
WITH NOFORMAT, NAME = N'File_TEST-Full Filegroup Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [File_TEST] TO  DISK = N'D:\MSSQL.1\MSSQL\Backup\File_TEST_LOG_05022009.trn' 
WITH NOFORMAT, NAME = N'File_TEST-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
 
 
RESTORE DATABASE [File_TEST] FILE = N'FileTest_Dev', FILEGROUP = 'Primary' --FILE = N'FileTest_Dev' 
FROM  DISK = N'D:\MSSQL.1\MSSQL\Backup\File_TEST_PRIMARY_05022009.bak' 
WITH  FILE = 1,  NOUNLOAD, STATS = 10, norecovery
GO
 
 
RESTORE DATABASE [File_TEST] FILE = N'FileTest_AppSupport',
FILEGROUP = 'AppSupport' --FILE = N'FileTest_AppSupport'
FROM  DISK = N'R:\FileTestUAT_appsupport.bak' WITH  FILE = 1,
MOVE N'FileTest_AppSupport' TO N'M:\DATAFILES\File_TEST.ndf',  Recovery, NOUNLOAD,  STATS = 10
GO
 
 
RESTORE LOG [File_TEST] 
FROM  DISK = N'D:\MSSQL.1\MSSQL\Backup\File_TEST_LOG_05022009.trn' 
WITH  FILE = 1,  RECOVERY, STATS = 10 --, stopat = '05-07-2009 00:00:00'
GO
 
RESTORE DATABASE File_TEST WITH RECOVERY
go

Open in new window

LVL 6
PsychoDazeyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
as per my belief, it is not possible even let me confirm few things first, I will get back to you with final answer soon.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I am quite sure you can restore a single filegroup.
if RieshShah does not come back, I can invest some time trying this out ...
(but you have to recall me, if needed)
0
RiteshShahCommented:
Hi A3, back from one database could not be restored to already running database on other server and in this case Author is asking to restore only one filegroup, I guess which is not possible from my end. If you can do it, than please go ahead, I will love to see the solution. :) I know some impossible thing could be possible by A3.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

PsychoDazeyAuthor Commented:
Hi A3 - I would appreciate any help you can give me!  I am stuck on this one.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I started working on it yesterday, not yet finished...
0
RiteshShahCommented:
eager to see its result, A3. :)
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I have been tempering around with this for several hours in the last evenings,
and came to the following conclusions:

* when you restore a filegroup, you MUST restore also the primary filegroup (aka that filegroup's backup must be in the same backup file/media), even if you use the PARTIAL option

* when you restore a filegroup, you MUST restore all other filegroups, otherwise those remain OFFLINE

so, I think, your best option is to put that data into a dedicated database, and not into a filegroup of the same database.
the explanation I deducted is that the filegroup backup/restore is only to make it possible to restore some essential filegroups before the others, making the applications partially available again.

note: I found this page with a good article about this, including some differences between 2000 and 2005, for example:
http://sqlserverpedia.com/wiki/Filegroup_Restore_Script


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PsychoDazeyAuthor Commented:
Thanks, I appreciate you looking into this for me.  I had come to pretty much the same conclusion but I was hoping I was wrong!
0
RiteshShahCommented:
so I was right that it is not possible to restore only one filegroup.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.