Rename SQL log and master database file

I am working on SQL Server 2014. I want to know how can I rename the .ldf and .mdf files of my database after creating it? If it is possible what are the steps?

You can't rename the files while they are in use, and don't make them "not in use" by stopping the SQL Server, it won't pick up on the change automatically.

The quickest way is as follows:

• Detach the database with EXEC sp_detach_db 'database_name' or the appropriate dialogue in SQL Server Management Studio.

The database must have no current connections otherwise SQL Server won't allow you to detach it - if you have trouble with this force that database into single user mode.

• Rename the files as needed

• Reattach the database which assuming you ave only a single data file and a single log files is done with EXEC sp_attach_db 'database_name', 'full_path_to_mdf', 'full_path_to_ldf', for example: EXEC sp_attach_db 'my_renamed_db', 'd:\dbs\data\my_renamed_db.mdf', 'd:\dbs\logs\my_renamed_db.ldf'

Again, the appropriate dialogue in SQL Server Management Studio can be used instead.

As you are specifying the database name in the call to sp_attach_db, this does the rename step also.

Update:

As Travis reminds me in the comments, sp_attach_db has been officially deprecated for a while and you should use the newer DDL method instead. The above example becomes:

CREATE DATABASE my_renamed_db
    ON (FILENAME = 'd:\dbs\data\my_renamed_db.mdf')
     , (FILENAME = 'd:\dbs\data\my_renamed_db.ldf')
FOR ATTACH;

Slightly safer even, you can avoid completely detaching the database at all by setting it offline and modifying the file locations instead (you still have to manually move or rename the files though):

ALTER DATABASE my_renamed_db SET OFFLINE;
ALTER DATABASE my_renamed_db MODIFY FILE (NAME='data_file_name', FILENAME='d:\dbs\data\my_renamed_db.mdf');
ALTER DATABASE my_renamed_db MODIFY FILE (NAME='log_file_name', FILENAME='d:\dbs\data\my_renamed_db.ldf');
ALTER DATABASE my_renamed_db SET ONLINE;

You can move/rename the files any time after the SET OFFLINE but before SET ONLINE.

If you don't know the logical filenames you can look them up in SSMS or via

SELECT f.name, f.physical_name 
FROM   master.sys.databases d 
JOIN   master.sys.master_files f 
       ON d.database_id = f.database_id 
WHERE  d.name = 'my_renamed_db'

If moving tempdb things are different: you can't offline it and it is recreated every time the server starts. Instead make the ALTER DATABASE MODIFY FILE calls and restart the SQL Server instance.

StackExchange Database Administrators Q#117692, answer score: 9