Move tempdb and don't want to use Detach method!
A new Developer within our testing environment asked me this question to see whether they can use DETACH/ATTACH method than using ALTER database method.
For the user databases it is available such method of DETACH/ATTACH method when moving the databases, but for system database it is not possible as they are in exclusive use for sqlserve.exe file. As by default configuration you will not be able to use detach/attach method for system databases. For more information about how to move system databases in SQL Server 2005, see the "Moving System Databases" topic in SQL Server Books Online. To view this topic, visit the following Microsoft Developer Network (MSDN) Web site:
Moving the tempdb database
| 1. |
Determine the logical file names for the tempdb database by using sp_helpfile as follows:use tempdb
go
sp_helpfile
go |
| 2. |
Use the ALTER DATABASE statement, specifying the logical file name as follows: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 Message 1
File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.
Message 2
File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server. |
| 3. |
Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server. |
| 4. |
Stop and then restart SQL Server. |
