I was verifying a server setup and found that the tempdb files were residing in the root location created during initial sql server installation. As a part of good practices I always separate out the tempdb files to a separate drive. To achieve this I had to use to the below sql.
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
In general, To move a data file or log file to a new location, specify the current logical file name in the NAME clause and specify the new path and operating system file name in the FILENAME clause. For example:
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )
For user databases, before using the sql above, you need to move the database files to the required location. Sql above does not actually move the database files. It just updates the system catalog.
When the sql is executed successfully. you should see the messages below,
The file "dev1" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "dev1_log" has been modified in the system catalog. The new path will be used the next time the database is started.
In order to connect to the database successfully, restart the sql server service.
Later, I had to restore a dev database to a staging server and wanted to keep the logical file names consistent. Dev database had the logical name
dev_aw_log for the mdf and ldf files respectively.
I used the below sql to change the logical name after the restore.
ALTER DATABASE aw_qa MODIFY FILE(NAME = dev1_aw, NEWNAME = qa_aw)
ALTER DATABASE aw_qa MODIFY FILE(NAME = dev1_aw_log, NEWNAME = qa_aw_log)
To modify the logical name of a data file or log file, specify the logical file name to be renamed in the NAME clause, and specify the new logical name for the file in the NEWNAME clause. For example:
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )
SQL Server Tips · · · ·