We are upgrading our hardware on a SQL Server 2000. We want to put all of our
non-clustered indexes on a separate hard drive and we want to put tempdb on
it's own drive.
Right now all tables and indexes are in Primary. We are setting up the
filegroups for the main database. Do we have to DTS everything to the new
Primary and then move filegroups for the tables and indexes that we want on
different drives? Can we automate changing the filegroups for the
non-clustered indexes with a script or do we have to do this table by table
and index by index?
How do we move TempDB to a different drive? Do we have to install SQL first?
If not, what should we do?
Thanks in advance.
One thing at a time.
Moving tempdb:
Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
There is a specific section on moving tempdb. BTW, the newest
recommendation is to have one file per processor in tempdb, even if they are
on the same spindle set. This is only for tempdb. I would do this first.
You can move a table from one filegroup to another by dropping and
recreating the clustered index and specifying a filegroup for the new table.
One alternative is to detach and attach as per the above KB article. If you
have only a very small maintenance window, then you can do a backup and
restore with norecovery to the new locationso you can apply transaction logs
to "catch up" when you switch over. Simply rename the old and the new
databases after finishing catching up and you are in business.
You will have to recreate each index on each table independently to move
them to the new filegroup. You can use the INFORMATION_SCHEMA views to
generate lists and with a little string manipulation you can create the
scripts almost automatically.
As always, test everything on a server you can afford to break.

Signature
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
> We are upgrading our hardware on a SQL Server 2000. We want to put all of
> our
[quoted text clipped - 16 lines]
>
> Thanks in advance.