Home > Management Studio, SQLServer > SQL Server – Renaming database and physical database files

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.

 

2. Renaming a Database using T-SQL:

Step 1: 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.

Now, we have renamed the database, but we still need to rename the database files.

 

Step 2: Renaming Files:

We can rename the logical name for database files using ALTER DATABASE:

— 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:

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:

Step 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

USE [master]

ALTER DATABASE [MyDatabase]

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

 

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

 

Step 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:

image

 

Step 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

USE [master]

CREATE DATABASE [SqlAndMe] ON

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

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

 FOR ATTACH

 

— Rename Logical file names

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')

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)

 

Hope This Helps!

Vishal

  1. Anthony
    21.03.2011 01:55 | #1

    Very helpful – thankyou Vishal! Your directions are a model of clarity.

  2. Erfan
    15.03.2012 07:00 | #2

    perfect……

  3. Khaled
    25.07.2012 02:32 | #3

    very nice

  4. sajish
    29.01.2013 01:57 | #4

    great job thank u

  5. paweln66
    14.08.2013 01:33 | #5

    First and second approach doesn’t work. It change DB name and logic name, but it doesn’t change names of .mdf and LDF files.

    But thank you for the good description!

  1. No trackbacks yet.