filegroup is offline after restore

I am using SQL-2005 Beta. I created 7 filegroups and associate them to
a table "Myhour" through partition function and schema. It works fine.
Then I tried the following statements to backup and retore the first
filegroup:
BACKUP DATABASE Mydb
FILE='20050204' -- my first filegroup
TO DISK='C:\backup\20050204.bak'
GO
RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
GO
The execution is OK. However, after that, I could not access data from
Myhour table:
SELECT * from Myhour
GO
The error message is that '20050204' filegroup is offline. I tried
many ways. I could not figure out how to bring this filegroup online.
I event tried to remove the filegroup. I got the same offline message.
Any way to bring filegroup online?
David Chu
Information regarding filegroup restore is available in Books Online. Basically, you restored part
of the database to an earlier point in time. You don't want SQL Server to be all happy and let you
use this possibly inconsistent database? So you need to apply the transaction log backups since the
database backup was performed so that SQL Server can re-apply the work that has been performed for
that filegroup. New for SQL Server 2005 is that you can set that filegroup to read-only (before the
backup and not change it back to read write) and then restore of transaction logs are not necessary
as SQL Server would know that no data has been changed in the filegroup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502090925.2966a624@.posting.google.c om...
>I am using SQL-2005 Beta. I created 7 filegroups and associate them to
> a table "Myhour" through partition function and schema. It works fine.
> Then I tried the following statements to backup and retore the first
> filegroup:
> BACKUP DATABASE Mydb
> FILE='20050204' -- my first filegroup
> TO DISK='C:\backup\20050204.bak'
> GO
> RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
> GO
> The execution is OK. However, after that, I could not access data from
> Myhour table:
> SELECT * from Myhour
> GO
> The error message is that '20050204' filegroup is offline. I tried
> many ways. I could not figure out how to bring this filegroup online.
> I event tried to remove the filegroup. I got the same offline message.
> Any way to bring filegroup online?
> David Chu
|||OK. I may do something not correctly to restore filegroup. For my
current position, is there way to bring my offline filegroup
(20050204) back on line? Without it back on line, I could not do
anything about my table Myhour because 20050204 is a partition of the
table.
|||Try backing up the transaction log, restore the file group and then restore the transaction log
backup. But check Books Online first. It was a while since I worked with filegroup backup and
restore, so I might be a bit rusty on the details.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502100818.72e989f3@.posting.google.c om...
> OK. I may do something not correctly to restore filegroup. For my
> current position, is there way to bring my offline filegroup
> (20050204) back on line? Without it back on line, I could not do
> anything about my table Myhour because 20050204 is a partition of the
> table.