Rename a Database and its MDF and LDF files in SQL Server

In Developing a solution or rebuild a solution some time we need to change database rename and it's data file. I found couple of solution in online . I am prefer to use T-SQL .

 
USE [master]
--Set Database to Single-User Mode
ALTER DATABASE [OLD_DB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

--Rename Database
ALTER DATABASE [OLD_DB] MODIFY Name = [NEW_DB]

--Set Database to Multi-User Mode
ALTER DATABASE [CSLMS] SET  MULTI_USER WITH ROLLBACK IMMEDIATE




--Rename Logical File Names
ALTER DATABASE [NEW_DB]
            MODIFY FILE (NAME=N'OLD_DB', NEWNAME=N'NEW_DB')
ALTER DATABASE [NEW_DB]
            MODIFY FILE (NAME=N'OLD_DB_log', NEWNAME=N'NEW_DB_log')


-- Checking Physical name
SELECT      name, physical_name
FROM        [CSLMS].sys.database_files


-- Detach Database
USE [master]
ALTER DATABASE [NEW_DB]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

EXEC master.dbo.sp_detach_db @dbname = N'CSLMS'
  

Now we rename the database physical files using Windows Explorer;

 
--Attach Database
USE [master]
CREATE DATABASE [NEW_DB] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\NEW_DB.mdf'),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\NEW_DB_log.ldf')
 FOR ATTACH
  

Successfully execute above sql we can easily rename database name and data files.

Read More