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.
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
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.
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)
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.