Renaming database and physical database files

1.Rename a Database using Management Studio:

• Renaming Database:

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

However, this does not change the database file names:

SQL Query: 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)

• Renaming Files:

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

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

2. Renaming a Database using T-SQL:

• Renaming Database:

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

 USE [master]

--Set Database to Single-User Mode

ALTER DATABASE [MyDatabase] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE




--Rename Database

ALTER DATABASE [MyDatabase] MODIFY Name = [SqlAndMe]




--Set Database to Multi-User Mode

ALTER DATABASE [SqlAndMe] SET  MULTI_USER WITH ROLLBACK IMMEDIATE

Result Set:

The database name 'SqlAndMe' has been set.

 

•  Renaming Files:

--Rename Logical File Names

ALTER DATABASE [SqlAndMe]

            MODIFY FILE (NAME=N'MyDatabase', NEWNAME=N'SqlAndMe')

ALTER DATABASE [SqlAndMe]

           MODIFY FILE (NAME=N'MyDatabase_log',NEWNAME=N'SqlAndMe_log')

Result Set:

The file name 'SqlAndMe' has been set.

The file name 'SqlAndMe_log' has been set.

 

However, none of these two methods can change the physical database file name, you can check this using sys.database_files:

SQL Query:SELECT      name, physical_name

FROM        [SqlAndMe].sys.database_files

 

Result Set:

name          physical_name

——---        —————------------

SqlAndMe      C:\…\MyDatabase.mdf

SqlAndMe_log  C:\…\MyDatabase_log.LDF


  

(2 row(s) affected)

 

Sometimes we also need to rename the physical database files, this cannot be done via two methods described above.

3. Renaming a Database using detach/attach:


1. Detach database:
You can detach a database either using Management Studio or T-SQL, To detach a database using Management Studio, right-click on database > go to "Tasks" > "Detach", click OK to detach a database,
To detach a database using T-SQL, you can use master..sp_detach_db procedure:

--Detach Database using sp_detach_db

SQL Query:USE [master]

          ALTER DATABASE [MyDatabase]

          SET SINGLE_USER WITH ROLLBACK IMMEDIATE




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

 2.Rename Physical files:
Once the database is detached, SQL Server releases the locks on physical files, now you can rename the physical files using Windows Explorer, or command prompt:

 

 

 3. Attaching database with New Name:
Once, the physical files have been renamed, you can attach the database using new database and file names:

-– Attach Database

SQL Query:USE [master]

          CREATE DATABASE [SqlAndMe] ON

          ( FILENAME = N'C:\…\SqlAndMe.mdf'),

          ( FILENAME = N'C:\…\SqlAndMe_log.LDF')

          FOR ATTACH

 

--Rename Logical file names

SQL Query: USE [SqlAndMe]

           ALTER DATABASE [SqlAndMe]

         MODIFY FILE (NAME=N'MyDatabase', NEWNAME=N'SqlAndMe')

          ALTER DATABASE [SqlAndMe]

         MODIFY FILE (NAME=N'MyDatabase_log', NEWNAME=N'SqlAndMe_log')




SQL Query: SELECT      name, physical_name

FROM        [SqlAndMe].sys.database_files

Result Set:

name          physical_name

————  —————–

SqlAndMe      C:\…\SqlAndMe.mdf

SqlAndMe_log  C:\…\SqlAndMe_log.LDF


 

(2 row(s) affected)