SQL Server – Renaming database and physical database files

Sometimes we need to change the name of the database, for example, a database might have been restored using a different name. Whatever the reason may be, this can be easily done via Management Studio or T-SQL.



1. Rename a Database using Management Studio:

Step 1: Renaming Database:

Right Click the Database Name in Object Explorer and Select "Rename", now you can specify new name:

image image

However, this does not change the database file names:

SELECT name, physical_name

FROM [SqlAndMe].sys.database_files

Result Set:

name physical_name

———— ——————-

MyDatabase C:\…\MyDatabase.mdf

MyDatabase_log C:\…\MyDatabase_log.LDF



(2 row(s) affected)



Step 2: Renaming Files:

To change filenames, Right Click on Database in Object Explorer and Select "Properties", Then, go to "Files" Tab:

image

Here, you can change the logical filenames for DATA and LOG files.