devxlogo

Renaming an SQL Server Database

Renaming an SQL Server Database

You can use the following query to rename an SQL Database:

USE masterGOALTER DATABASE YOURDATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATEGOEXEC master..sp_renamedb 'YOURDATABASENAME','YOUR_NEW_DATABASE_NAME'GOALTER DATABASE YOUR_NEW_DATABASE_NAME SET MULTI_USER GO

The only problem, well, not really a problem, is that although the Database’s name has been changed, the physical Logical Name and File Name has not been renamed. You should also execute code similar to the following to rename the Logical Name and File Name as well:

ALTER DATABASE YOURDATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE-- Change Logical File Name ALTER DATABASE [YOURDATABASENAME] MODIFY FILE (NAME=N'YOURDATABASENAME', NEWNAME=N'YOUR_NEW_DATABASE_NAME')GOALTER DATABASE [YOURDATABASENAME] MODIFY FILE (NAME=N'YOURDATABASENAME_log', NEWNAME=N'YOUR_NEW_DATABASE_NAME_log')GO--DetachGOEXEC master.dbo.sp_detach_db @dbname = N'YOURDATABASENAME'GO--RenameEXEC xp_cmdshell 'RENAME "C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAYOURDATABASENAME.mdf", "YOUR_NEW_DATABASE_NAME.mdf"'GOEXEC xp_cmdshell 'RENAME "C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAYOURDATABASENAME_log.ldf", "YOUR_NEW_DATABASE_NAME_log.ldf"'GO--AttachCREATE DATABASE YOUR_NEW_DATABASE_NAME ON ( FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAYOUR_NEW_DATABASE_NAME.mdf' ),( FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAYOUR_NEW_DATABASE_NAME_log.ldf' )FOR ATTACHGOALTER DATABASE YOUR_NEW_DATABASE_NAME SET MULTI_USER GO
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist