Link to home
Start Free TrialLog in
Avatar of PsychoDazey
PsychoDazey

asked on

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

Avatar of RiteshShah
RiteshShah
Flag of India image

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.
Avatar of Guy Hengel [angelIII / a3]
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)
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.
Avatar of PsychoDazey
PsychoDazey

ASKER

Hi A3 - I would appreciate any help you can give me!  I am stuck on this one.
I started working on it yesterday, not yet finished...
eager to see its result, A3. :)
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
so I was right that it is not possible to restore only one filegroup.