How to rename a SQL Server Database

To rename a database we need need to rename both the logical and physical database files. The logical file name is the name used to refer to the physical file in all Transact-SQL statements. Following steps could be followed to rename a SQL Server setps:

I. Below query will retrieve currently set Logical and Physical File name:   

 SELECT * FROM sys.master_files AS mf

 SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalFileName, physical_name AS PhysicalFileName FROM sys.master_files AS mf

In the above query sys.master_files is a view available in master database.

II. Rename the logical file name. Database must be online while changing logical file name. Query syntax is as follows:
ALTER DATABASE <DBName> MODIFY FILE (NAME = '<logicalFile>', NEWNAME = '<newlogicalfilename>') 

 --For Example
ALTER DATABASE Test MODIFY FILE (NAME='Test', NEWNAME='NewTest')

 ALTER DATABASE Test MODIFY FILE (NAME='Test_log', NEWNAME='NewTest_Log')

III. Rename the physical file name. Database must be online while changing physical file name. Query syntax is as follows:
--ALTER DATABASE <DBName> MODIFY FILE (NAME = '<logicalFile>', FILENAME = '<physicalname>')

 --For Example
ALTER DATABASE Test MODIFY  FILE (NAME='NewTest', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\NewTest.mdf')

 ALTER DATABASE Test MODIFY  FILE (NAME='NewTest_Log', FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\NewTest_1.ldf')

IV. Take the database offline

V. Rename the physical files manually

VI. Reattach the database.