SQL Server 2005/2008 – Renaming database and physical database files using T-SQL

Step1: Change Database Name



To Rename a database using T-SQL, use below script:

USE [master]

– Set Database to Single-User Mode

ALTER DATABASE [DBNAME] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

– Rename Database

ALTER DATABASE [DBNAME] MODIFY Name = [DBNAME2]

– Set Database to Multi-User Mode

ALTER DATABASE [DBNAME2] SET  MULTI_USER WITH ROLLBACK IMMEDIATE

Step2: Rename Files.

ALTER DATABASE [ DBNAME2 ]

            MODIFY FILE (NAME='DBNAME', NEWNAME='DBNAME2.mdf')

ALTER DATABASE [ DBNAME2 ]

            MODIFY FILE (NAME='DBNAME_log', NEWNAME='DBNAME2_log')





However, this method can change the physical database file name, you can check this:


SELECT      name, physical_name

FROM        [DBNAME].sys.database_files