Sql And Me

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:

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:

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:

 

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