How to drop offline file/filegroups after piecemeal restore

I have a very large database (5 TB) with many file groups. Basically each
file group consists of one large table (70 - 80 GB). The backup structure is
set up to backup each filegroup after it's table is loaded (the data is
static) and then perform a differetial backup. The server crashed the other
day and I began the restore process. Unfortunately due to space limitations,
all of the file group backup files were not available. So I did a piecemeal
restore with the primary FG backup file and then all of the other available
FG backup files. The last differential file was restored with RECOVERY and
the database is now online. However there are about 30 file groups offline
now. How do I drop/remove these file/filegroups.
I can not remove the files with the alter database statement, this gets the
error:
Cannot add, remove, or modify a file in filegroup 'FG0001' because the
filegroup is offline
I am running SQL Server 2005 Enterprise edition (sp2)
Thanx in advance for any and all help,
--
Jeff Carrington
DBA
ComscoreAccording to Books Online, you should be able to use ALTER DATABASE ... REMOVE FILE to get rid of a
"defunkt" file and filegroup:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/055f9c6a-5c18-4942-98e7-ec918f0ff975.htm
Did you do the initial restore using the PARTIAL option?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jeff Carrington" <JeffCarrington@.discussions.microsoft.com> wrote in message
news:A3407889-76CA-45E5-89BE-19765A67639B@.microsoft.com...
>I have a very large database (5 TB) with many file groups. Basically each
> file group consists of one large table (70 - 80 GB). The backup structure is
> set up to backup each filegroup after it's table is loaded (the data is
> static) and then perform a differetial backup. The server crashed the other
> day and I began the restore process. Unfortunately due to space limitations,
> all of the file group backup files were not available. So I did a piecemeal
> restore with the primary FG backup file and then all of the other available
> FG backup files. The last differential file was restored with RECOVERY and
> the database is now online. However there are about 30 file groups offline
> now. How do I drop/remove these file/filegroups.
> I can not remove the files with the alter database statement, this gets the
> error:
> Cannot add, remove, or modify a file in filegroup 'FG0001' because the
> filegroup is offline
> I am running SQL Server 2005 Enterprise edition (sp2)
> Thanx in advance for any and all help,
> --
> Jeff Carrington
> DBA
> Comscore|||Yes I did use the PARTIAL option when executing the first restore of the
primary file group. I read the same section in BOL, but my attempt to remove
the file gets the error stating the file group is offline.
Thanx for the help...
--
Jeff Carrington
"Tibor Karaszi" wrote:
> According to Books Online, you should be able to use ALTER DATABASE ... REMOVE FILE to get rid of a
> "defunkt" file and filegroup:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/055f9c6a-5c18-4942-98e7-ec918f0ff975.htm
> Did you do the initial restore using the PARTIAL option?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jeff Carrington" <JeffCarrington@.discussions.microsoft.com> wrote in message
> news:A3407889-76CA-45E5-89BE-19765A67639B@.microsoft.com...
> >I have a very large database (5 TB) with many file groups. Basically each
> > file group consists of one large table (70 - 80 GB). The backup structure is
> > set up to backup each filegroup after it's table is loaded (the data is
> > static) and then perform a differetial backup. The server crashed the other
> > day and I began the restore process. Unfortunately due to space limitations,
> > all of the file group backup files were not available. So I did a piecemeal
> > restore with the primary FG backup file and then all of the other available
> > FG backup files. The last differential file was restored with RECOVERY and
> > the database is now online. However there are about 30 file groups offline
> > now. How do I drop/remove these file/filegroups.
> >
> > I can not remove the files with the alter database statement, this gets the
> > error:
> > Cannot add, remove, or modify a file in filegroup 'FG0001' because the
> > filegroup is offline
> >
> > I am running SQL Server 2005 Enterprise edition (sp2)
> >
> > Thanx in advance for any and all help,
> > --
> > Jeff Carrington
> > DBA
> > Comscore
>
>