August 14, 2009 at 10:47 am
Can anyone tell me if I can change the drive letters for tempdb data and log files? I want to move the files from their default locations to another locations but this is proofing to be an impossible tasks as I was unable to detach and attach the files. Kindly, tell me if this is possible and how i can go about it.
Many thanks.
Sahoong.
August 14, 2009 at 10:57 am
You can actually find this in BOL (Books Online, the SQL Server Help System). You can access it from SSMS by pressing the {f1} function key.
here is the relevant part:
A. Moving the tempdb database
The following example moves the tempdb data and log files to a new location as part of a planned relocation.
Note:
Because tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files. The files are created in the new location when the service is restarted in step 3. Until the service is restarted, tempdb continues to use the data and log files in existing location.
1. Determine the logical file names of the tempdb database and their current location on the disk.
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'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 = 'F:\SQLLog\templog.ldf');
GO
3. Stop and restart the instance of SQL Server.
4. Verify the file change.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
5. Delete the tempdb.mdf and templog.ldf files from the original location.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply