Best Practice for Creating & Renaming a SQL Database


You can easily use the sp_renamedb system stored procedure which helps renaming a SQL Server Database. However, the drawback of using the same is that it will not rename the Logical and Physical names of the underlying database files.

It’s a best practice to make sure the Logical Name and Physical File Name of the database is also renamed to reflect the actual name of the database to avoid any confusion with backup, restore or detach/attach operations.

In this article, you will see the steps which you need to follow to rename a SQL Server Database using the ALTER DATABASE command.

Rename CoreDB Database Using sp_renamedb System Stored Procedure

Now let’s rename the MyDB database to ProductsDB by executing the below T-SQL code.
USE master
GO
ALTER DATABASE MyDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
EXEC master..sp_renamedb ‘MyDB’,’ProductsDB’
GO
ALTER DATABASE ProductsDB
SET MULTI_USER
GO

Steps to Rename a SQL Server Database
DBAs should follow the below steps which will not only rename the database, but at the same time will also rename the Logical Name and File Name of the database.

This first set of commands put the database in single user mode and also modifies the logical names.

/* Set Database as a Single User */
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/* Change Logical File Name */
ALTER DATABASE [ProductsDB] MODIFY FILE (NAME=N’MyDB’, NEWNAME=N’ProductsDB’)
GO
ALTER DATABASE [ProductsDB] MODIFY FILE (NAME=N’MyDB_log’, NEWNAME=N’ProductsDB_log’)
GO

Now we need to detach the database, so we can rename the physical files.  If the database files are open you will not be able to rename the files.

/* Detach Current Database */
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N’CoreDB’
GO

Once the CoreDB database is detached successfully then the next step will be to rename the Physical Files. This can be done either manually or by using the xp_cmdshell system stored procedure. You can enable xp_cmdshell feature 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

Once xp_cmdshell is enabled you can use the below script to rename the physical files of the database.

 /* Rename Physical Files */
USE [master]
GO
EXEC xp_cmdshell ‘RENAME “C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAMyDB.mdf”, “ProductsDB.mdf”‘
GO
EXEC xp_cmdshell ‘RENAME “C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAMyDB_log.ldf”, “ProductsDB_log.ldf”‘
GO

Once the above step has successfully executed then the next step will be to attach the database, this can be done by executing the T-SQL below:

 /* Attach Renamed ProductsDB Database Online */
USE [master]
GO
CREATE DATABASE ProductsDB ON
( FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAProductsDB.mdf’ ),
( FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAProductsDB_log.ldf’ )
FOR ATTACH
GO

You can verify the Logical and File Names for the ProductsDB database by executing the T-SQL below:

USE master
GO
/* Identify Database File Names */
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’ProductsDB’)
GO

Advertisements

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

%d bloggers like this: