Move TempDB data/trans From C: to E:

I have a SQL Server 2000 database server on Windows 2000 Advanced Server.
What is the best way to move the TempDB data and transaction log files from
the C: to E: drive?
Thank You,I'm very interested in this as well. We were planning on doing this first
thing Saturday morning.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...
> I have a SQL Server 2000 database server on Windows 2000 Advanced Server.
> What is the best way to move the TempDB data and transaction log files
> from
> the C: to E: drive?
> Thank You,|||Here ya go....BOL...just tried it and it worked great.
1. stop sql server
2. copy to your desired location
3. start sql server
4. run the alter statements below
5. restart sql server
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
GO
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uPtIzWxaGHA.2124@.TK2MSFTNGP03.phx.gbl...
> I'm very interested in this as well. We were planning on doing this first
> thing Saturday morning.
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:D7969E35-E4D2-4D22-A9BB-DD7B52EA6E2D@.microsoft.com...
>|||Hi Tim
What exactly are you doing in step 2? You shouldn't have to move anything,
since the tempdb files are recreated upon system startup. To move tempdb you
just need to run ALTER DATABASE and then restart your SQL Server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uRzZ4dxaGHA.3408@.TK2MSFTNGP04.phx.gbl...
> Here ya go....BOL...just tried it and it worked great.
> 1. stop sql server
> 2. copy to your desired location
> 3. start sql server
> 4. run the alter statements below
> 5. restart sql server
> USE master;
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
> GO
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uPtIzWxaGHA.2124@.TK2MSFTNGP03.phx.gbl...
>|||I was doing the steps as outlined in BOL...and I quote from BOL
G. Moving tempdb to a new location
The following example moves tempdb from its current location on disk to
another disk location.
1.. Determine the logical file names of the tempdb database and their
current location on disk.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO
2.. Physically move these files to the new location E:\SQLData.
In this example the new location specified for the tempdb files is
E:\SQLData. If you move the files to different location, modify the example
to point to that location.
3.. Change the location of each file by using ALTER DATABASE.
4.. Stop and restart the instance of SQL Server.
5.. Verify the file change.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uG0CSzxaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Hi Tim
> What exactly are you doing in step 2? You shouldn't have to move anything,
> since the tempdb files are recreated upon system startup. To move tempdb
> you just need to run ALTER DATABASE and then restart your SQL Server.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uRzZ4dxaGHA.3408@.TK2MSFTNGP04.phx.gbl...
>|||Hi Tim
Just FYI, you do not have to move any files to move tempdb, because tempdb i
s rebuilt every time SQL Server starts.
Do you have the latest BOL? There was a refresh just a week ago.
G. Moving tempdb to a new location
The following example moves tempdb from its current location on the disk to
another disk location. Because tempdb is re-created each time the MSSQLSERVE
R service is started, you do not have to physically move the data and log fi
les. The files are created when the service is restarted in step 3. Until th
e service is restarted, tempdb continues to function in its existing locatio
n.
1.. Determine the logical file names of the tempdb database and their curren
t location on disk.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO
2.. Change the location of each file by using ALTER DATABASE.
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
GO
3.. Stop and restart the instance of SQL Server.
4.. Verify the file change.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
5.. Delete the tempdb.mdf and templog.ldf files from their original location
.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message news:%23TTfa5zaGHA.1196
@.TK2MSFTNGP03.phx.gbl...
>I was doing the steps as outlined in BOL...and I quote from BOL
>
> G. Moving tempdb to a new location
> The following example moves tempdb from its current location on disk to
> another disk location.
>
> 1.. Determine the logical file names of the tempdb database and their
> current location on disk.
>
>
>
> SELECT name, physical_name
> FROM sys.master_files
> WHERE database_id = DB_ID('tempdb');
> GO
>
> 2.. Physically move these files to the new location E:\SQLData.
>
>
>
> In this example the new location specified for the tempdb files is
> E:\SQLData. If you move the files to different location, modify the exampl
e
> to point to that location.
>
>
>
> 3.. Change the location of each file by using ALTER DATABASE.
>
> 4.. Stop and restart the instance of SQL Server.
>
>
> 5.. Verify the file change.
>
>
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uG0CSzxaGHA.1192@.TK2MSFTNGP03.phx.gbl...
>
>