Tag: Renaming a database

I used to rename a database like this.

ALTER DATABASE ConorDB MODIFY NAME = KhabibDB;

But if you use the above method, the logical name and file name will not change. Let’s verify using the script below.

USE master
GO

SELECT 
name AS [Logical Name], 
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State] 
FROM sys.master_files
WHERE database_id = DB_ID(N'KhabibDB')
GO

So you will notice that, even though we renamed the database to KhabibDB, the logical name and file name is still ConorDB. Nevertheless, you have accomplished your objective of renaming your database.

But wait! There is a better way of renaming SQL Server databases that I’m going to show you now!

The procedures below will not only rename your database, but it will also rename the Logical Name and the File Name of the database.

A Better Way To Rename a SQL Server Database

1. Place database in a single user mode.

ALTER DATABASE [ConorDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

2. Modify the logical names.

ALTER DATABASE [ConorDB] MODIFY FILE (NAME=N'ConorDB_data', NEWNAME=N'KhabibDB_data')
GO
ALTER DATABASE [ConorDB] MODIFY FILE (NAME=N'ConorDB_log', NEWNAME=N'KhabibDB_log')
GO

3. Detach the database so that we can rename the physical data files.

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'ConorDB'
GO

4. After we have detach the database successfully, we can now rename the physical data files. We can do this either manually or by using xp_cmdshell system stored procedure. The xp_cmdshell feature can be enabled using the sp_configure system stored procedure.

USE master
GO
sp_configure 'show advanced options'
GO
/* 0 = Disabled , 1 = Enabled */
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO

5. Now that xp_cmdshell is enabled, we can proceed to rename the physical files of the database using the script below.

USE [master]
GO
EXEC xp_cmdshell 'RENAME "S:\Demo\ConorDB_data.mdf", "KhabibDB_data.mdf"'
GO
EXEC xp_cmdshell 'RENAME "S:\Demo\ConorDB_log.ldf", "KhabibDB_log.ldf"'
GO

6. After that’s done, let’s attach the database using the script below.

USE [master]
GO
CREATE DATABASE KhabibDB ON 
( FILENAME = N'S:\Demo\KhabibDB_data.mdf' ),
( FILENAME = N'S:\Demo\KhabibDB_log.ldf' )
FOR ATTACH
GO

7. You may rename the actual database now if you have not done so.

ALTER DATABASE ConorDB MODIFY NAME = KhabibDB;

8. Lastly, let’s allow multi-user access for the database.

ALTER DATABASE KhabibDB SET MULTI_USER 
GO

9. Use the script below the verify the Logical and Physical File Names of the database.

USE master
GO

SELECT 
name AS [Logical Name], 
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State] 
FROM sys.master_files
WHERE database_id = DB_ID(N'KhabibDB')
GO