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

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. Anthony
    October 21, 2011 at 1:55 am

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

  2. Erfan
    March 15, 2012 at 7:00 pm

    perfect……

  3. Khaled
    July 25, 2012 at 2:32 pm

    very nice

  4. sajish
    January 29, 2013 at 1:57 pm

    great job thank u

  5. August 14, 2013 at 1:33 pm

    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. August 4, 2011 at 7:55 pm
  2. August 14, 2013 at 8:26 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 285 other followers

%d bloggers like this: