تغییر مسیر بانک tempdb

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.

Moving the tempdb database