Re: Migrating to new hardward

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.

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP





"RossT" <RossT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:75CAE69D-E982-4627-BFC8-37427D798D8C@xxxxxxxxxxxxxxxx
> 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.


.

Relevant Pages

  • Re: Checkpointing Not Happening in Simple Recovery Model
    ... She says that she has simple recovery and that one of them is tempdb. ... Columnist, SQL Server Professional ... >> We have at least two databases on this server in simple recovery model. ...
    (microsoft.public.sqlserver.server)
  • Re: Checkpointing Not Happening in Simple Recovery Model
    ... I am looking at databases set to simple: ... > Columnist, SQL Server Professional ... >> She says that she has simple recovery and that one of them is tempdb. ... >> Yes, you are right, I read that Michelle is writing about logs not log, ...
    (microsoft.public.sqlserver.server)
  • Re: tempDB causing Errors 1101 and 1105
    ... During your processing, it fills up TempDB, and once you get this error ... Mike Epprecht, Microsoft SQL Server MVP ... > are no more pages available in filegroup DEFAULT. ... > adding additional files, or allowing file growth.. ...
    (microsoft.public.sqlserver.msde)
  • Re: specifying drive locations during SQL 2005 installation
    ... is it possible--during the installation of SQL Server 2005 Standard Edition--to specify the locations of the databases and especially the master and tempdb databases? ...
    (microsoft.public.sqlserver.setup)
  • Re: Global variable
    ... across all databases? ... You could use tempdb, etc. ... It sounds like you're "programming" though whereas in SQL you should think ... > What is recommended/prefered way to create global variable in SQL Server ...
    (microsoft.public.sqlserver.programming)