This post will provide guidance on how to amend the logical and physical file names of a MSSQL database.
When a copy of a database is restored as a new database, the logical file names will remain the same as the source database.
Firstly, check the current logical and physical file names:
USE master
GO
SELECT name AS [Logical_name],
physical_name AS [File_Path],
type_desc AS [File_Type],
state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N'Database_name')
GO
Running this query against a database called ‘SSMATEST’ on one of my database servers brings back the following:
Logical_name File_Path File_Type State DIRUT D:\Data\DIRUT.mdf ROWS ONLINE DIRUT_log D:\Logs\DIRUT_log.ldf LOG ONLINE
As can be seen, the physical names and logical names don’t match up the name of the database.
Let’s start with the logical names…
ALTER DATABASE [SSMATEST] MODIFY FILE (NAME='DIRUT', NEWNAME='SSMATEST'); GO ALTER DATABASE [SSMATEST] MODIFY FILE (NAME='DIRUT_log', NEWNAME='SSMATEST_log'); GO
We use pass the current name of the logical file – NAME – and then name that we wish to use as the new name – NEWNAME.
The changes can be verified by running the query at the beginning of the post, the results will show:
Logical_name File_Path File_Type State SSMATEST D:\Data\DIRUT.mdf ROWS ONLINE SSMATEST_log D:\Logs\DIRUT_log.ldf LOG ONLINE
So, that’s starting to look better, let’s move on to the physical file names.
First, take the database offline, thanks to Perry Whittle for suggesting the use of one ALTER DATABASE statement to achieve the same result as two!
It should be pointed out that you will need to carry this out during a maintenance window if the database is part of a live/production system.
ALTER DATABASE [SSMATEST] SET OFFLINE WITH ROLLBACK IMMEDIATE; GO
Now rename the files from DIRUT.mdf and DIRUT_log.ldf to SSMATEST.mdf and SSMATEST_log.ldf in the file system via File Explorer or DOS. Once that is done, return to SSMS.
Update the records in the system catalog.
ALTER DATABASE [SSMATEST] MODIFY FILE (Name='SSMATEST', FILENAME='D:\Data\SSMATEST.mdf') GO ALTER DATABASE [SSMATEST] MODIFY FILE (Name='SSMATEST_log', FILENAME='D:\Logs\SSMATEST_log.ldf') GO
Check the message to ensure that there were no problems.
The file "SSMATEST" has been modified in the system catalog. The new path will be used the next time the database is started. The file "SSMATEST_log" has been modified in the system catalog. The new path will be used the next time the database is started.
Bring the database back online.
ALTER DATABASE [SSMATEST] SET ONLINE; GO
Again, use the query at the top of the post to verify the changes are all good.
Logical_name File_Path File_Type State SSMATEST D:\Data\SSMATEST.mdf ROWS ONLINE SSMATEST_log D:\Logs\SSMATEST_log.ldf LOG ONLINE
There we have it!
Both the logical and physical file names have been updated to reflect the name of our database.
If you are new to T-SQL then I recommend checking out this book from the “Sams Teach Yourself” series:
