Restoring FileGroups...

We are currently in the process of designing a Consolidated System (data for multiple organizations stored in one database server) using SQL Server 2005. One of the design goal is to refresh individual company data from Production to "User Acceptance Server" without affecting other companies.

So, we thought of implementing this via Partitioning by Org. We need the "Proof of concept" that this can be done with a simple example. Since our DBA's are also new to SQL 2005, we are working together in achieving the goal. Ok, here is what we are trying to achieve:

1. Create a Database (Test1) with two file Groups (FileGroup1, FileGroup2) with two files in each file group.

2. Create two tables on this database, one on each FileGroup

Table 1 on FileGroup1, Table 2 on FileGroup2.

3. Populate data on these tables.

4. Create an exact copy of this database with the above architecture and call it Test2.

5. Populate Test2, with a different set of data.

6. Backup FileGroup2 on Test2 and restore it to Test1 database.

What we did:

1. I did the Full Database Backup of Test2 via SQL Management Studio. Also the log file with:I did the Full Database Backup of Test2 via SQL Management Studio. Also the log file using :

BACKUP LOG PGM TO DISK='D:\Test2_Log.bak' WITH NORECOVERY

2. Restore the database filegroup using:

RESTORE DATABASE Test1

FILEGROUP = 'FileGroup2'

FROM DISK ='D:\Test2.bak' WITH

MOVE 'FileName2_A' TO 'G:\Data\File2_A.ndf',

MOVE 'FileName2_B' TO 'G:\Data\File2_B.ndf'

3. . RESTORE LOG PGM

FROM DISK = 'D:\Test2_Log.bak'

WITH RECOVERY

After restoring the backup filegroup to Test1 database, I'm not able to query the table that resides in the filegroup that was restored. Error returned is:

Msg 8653, Level 16, State 1, Line 2

The query processor is unable to produce a plan for the table or view 'Junk2' because the table resides in a filegroup which is not online.

We know, we are missing some pieces while creating the backup or restoring that is causing this problem. If somebody can point it out, or provide us a "Working Example"...

Thanks for your help.

Take a full backup
BACKUP DATABASE _BIMAL_FG_TEST
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
GO

Or take File group backups
BACKUP DATABASE _BIMAL_FG_TEST
FILEGROUP = 'PRIMARY'
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FG_PRIMARY.bak'

BACKUP DATABASE _BIMAL_FG_TEST
FILEGROUP = 'FileGroup_A'
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FG_A.bak'

BACKUP DATABASE _BIMAL_FG_TEST
FILEGROUP = 'FileGroup_B'
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FG_B.bak'

You must take a log backup
BACKUP LOG _BIMAL_FG_TEST
TO DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_LOG.TRN'

On the target database you have to first restore the Primary FG

RESTORE DATABASE _BIMAL_FG_TEST_COPY
FILEGROUP = 'PRIMARY'
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
WITH MOVE '_BIMAL_FG_TEST' to 'O:\SQLData\_BIMAL_FG_TEST_COPY.mdf'
, MOVE '_BIMAL_FG_TEST_log' to 'P:\SQLLog\_BIMAL_FG_TEST_COPY_log.LDF'
,STATS=20
,NORECOVERY, REPLACE, PARTIAL

Then Restore the FileGroup you want
RESTORE DATABASE _BIMAL_FG_TEST_COPY
FILEGROUP = 'FileGroup_A'
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
WITH MOVE 'BIMAL_FILEGROUP_TEST_1' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_1.ndf'
,MOVE 'BIMAL_FILEGROUP_TEST_2' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_2.ndf'
,STATS=20
,REPLACE
,NORECOVERY

And then restore the Log backup to complete the recovery
RESTORE LOG _BIMAL_FG_TEST_COPY
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_LOG.TRN'
WITH RECOVERY

And if you want to restore another file group just followt the last two steps.
RESTORE DATABASE _BIMAL_FG_TEST_COPY
FILEGROUP = 'FileGroup_B'
FROM DISK = 'O:\Test_Backups\_BIMAL_FG_TEST_FULL.bak'
WITH MOVE 'BIMAL_FILEGROUP_TEST_3' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_3.ndf'
,MOVE 'BIMAL_FILEGROUP_TEST_4' to 'O:\SQLData\BIMAL_FILEGROUP_TEST_COPY_4.ndf'
,STATS=20
,NORECOVERY

RESTORE LOG _BIMAL_FG_TEST_COPY
FROM DISK = 'OTest_Backups\_BIMAL_FG_TEST_LOG.TRN'
WITH RECOVERY

This may not solve your problem but this will give you a working example you can play to see whether what you need is possible or not.