file group question

Hi
I have 4 filegroups in sql server 2000. 2 indexes and 2 tables.
They are 4 different disks and different disk controllers.
I do not have a backup of the database. Now I have a situation where I lost
one of the disk containing the index filegroup. I am running in FULL MODE.
I do not care if I loose index file group as it will only slow down the
database.
Is there a way to start the database without the index filegroup?
Can I take it offline or drop it?
Thanks
Mangesh
Mangesh Deshpande wrote:
> Hi
> I have 4 filegroups in sql server 2000. 2 indexes and 2 tables.
> They are 4 different disks and different disk controllers.
> I do not have a backup of the database. Now I have a situation where
> I lost one of the disk containing the index filegroup. I am running
> in FULL MODE.
> I do not care if I loose index file group as it will only slow down
> the database.
> Is there a way to start the database without the index filegroup?
> Can I take it offline or drop it?
> Thanks
> Mangesh
Do you think there were any clustered indexes in the database. If so,
where did you put those tables? On one of the table filegroups or one of
the index file groups? The table goes with the clustered index, so if
you built a clustered index on a table and put it on the index drive you
lost, you lost the table as well.
Many here are going to wonder:
Where is your redundancy on your drive subsystem?
Why no backups?
It's pretty risky leaving both out of the equation as you now know.
Going forward I might suggest using two mirrored sets. One for data and
one for temp db and log files.
David Gugick
Imceda Software
www.imceda.com
|||Thanks David. This is just a hypothetical case. For production we are using
RAIDa 5.
I am keeping clustered index on the my table file group and it is not broken.
My non clustered indexes are on separate file group and it is broken.
So I want to have a mechanism by which even if the index file group disk
crashes my system should be up. In oracle you can take index file system
offline and allow the database to work fine.
I wanted to check if we have a mechanism in SQL Server 2000.
Thanks always
Mangesh
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> Do you think there were any clustered indexes in the database. If so,
> where did you put those tables? On one of the table filegroups or one of
> the index file groups? The table goes with the clustered index, so if
> you built a clustered index on a table and put it on the index drive you
> lost, you lost the table as well.
> Many here are going to wonder:
> Where is your redundancy on your drive subsystem?
> Why no backups?
> It's pretty risky leaving both out of the equation as you now know.
> Going forward I might suggest using two mirrored sets. One for data and
> one for temp db and log files.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Mangesh Deshpande wrote:
> Thanks David. This is just a hypothetical case. For production we are
> using RAIDa 5.
> I am keeping clustered index on the my table file group and it is not
> broken. My non clustered indexes are on separate file group and it is
> broken.
> So I want to have a mechanism by which even if the index file group
> disk crashes my system should be up. In oracle you can take index
> file system offline and allow the database to work fine.
> I wanted to check if we have a mechanism in SQL Server 2000.
> Thanks always
> Mangesh
But in production you are using RAID 5 which won't apply to your
hypothetical. I personally have no idea whether SQL Server can recover
from a lost drive with a lost filegroup. I assume there is a way.
Whether that's with the help of MS PSS or some technique I don;t know
about I'm not sure. My only question here is why is this hypothetical
even worth considering if your production environment does not resemble
what you describe?
David Gugick
Imceda Software
www.imceda.com
|||Thanks David for sharing the knowledge. I was thinking of adding the
filegroups on one of our other production NON CRITICAL database which has no
RAID.
But I was checking to see if I can achieve any benefit and JUST Curious as
it is a standard practice in Oracle to Create indexes on separate Tablespaces.
Mangesh
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> But in production you are using RAID 5 which won't apply to your
> hypothetical. I personally have no idea whether SQL Server can recover
> from a lost drive with a lost filegroup. I assume there is a way.
> Whether that's with the help of MS PSS or some technique I don;t know
> about I'm not sure. My only question here is why is this hypothetical
> even worth considering if your production environment does not resemble
> what you describe?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>