Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Best Practice for renaming a SQL Server Database

MSSQLTips author Ashish Kumar Mehta By:   |   Read Comments (9)   |   Related Tips: More > DBA Best Practices

Problem
One of the Junior SQL Server Database Administrator in my company approached me yesterday with a dilemma. He was assigned a task to rename few of the databases in Beta and Production environments; the reason being the database name was based on some other project that is no longer relevant to the data which is presently stored within the database. At first I started to tell him, but figured it would be smarter to document the same and share the information.

Solution
Database Administrators usually use the sp_renamedb system stored procedure to quickly rename a SQL Server Database. However, the drawback of using sp_renamedb is that it doesn't 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 tip, you will see the steps which you need to follow to rename a SQL Server Database using the ALTER DATABASE command.


Creating a Sample Database Namely CoreDB

Let's first create a new database named CoreDB using the T-SQL below:

USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CoreDB')
DROP DATABASE CoreDB
GO
USE master
GO
CREATE DATABASE [CoreDB] 
ON PRIMARY 
( 
NAME = N'CoreDB', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CoreDB.mdf' , 
SIZE = 2048KB , 
FILEGROWTH = 1024KB 
)
LOG ON 
( 
NAME = N'CoreDB_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CoreDB_log.ldf' , 
SIZE = 1024KB , 
FILEGROWTH = 10%
)
GO

Rename CoreDB Database Using sp_renamedb System Stored Procedure

Now let's rename the CoreDB database to ProductsDB by executing the below T-SQL code.

USE master
GO
ALTER DATABASE CoreDB 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE
GO
EXEC master..sp_renamedb 'CoreDB','ProductsDB'
GO
ALTER DATABASE ProductsDB 
SET MULTI_USER 
GO

Once the above T-SQL has executed successfully the database name will change however the Logical Name and File Name will not change. You can verify this 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

Your output should look something like this from the above query.

You can see in the above snippet that the Logical Name and File Name in the DB File Path column for ProductsDB are still reflecting the old name of CoreDB. This is not a good practice to follow in a Production Environment. Below you will see the steps which a DBA can follow to rename the database and its respective files.


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 CoreDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/* Change Logical File Name */
ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB', NEWNAME=N'ProductsDB')
GO
ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB_log', NEWNAME=N'ProductsDB_log')
GO

This is the output from the above code.

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 Files\Microsoft SQL ServerMSSQL10.SQL2008\
MSSQL\DATA\CoreDB.mdf", "ProductsDB.mdf"' GO EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\
MSSQL\DATA\CoreDB_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 Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.ldf' )
FOR ATTACH
GO

Once the above step has successfully executed then the final step will be to allow multi user access for the user database by executing the below T-SQL:

/* Set Database to Multi User*/
ALTER DATABASE ProductsDB SET MULTI_USER 
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

You can see in the above snippet that the Logical Name and File Name for ProductsDB are now correct.

Next Steps

  • Once the database name is changed successfully then you need to make sure your application code is referring to the new database name.
  • The steps to rename a SQL Server Database mentioned in this tip are applicable for both SQL Server 2005 and SQL Server 2008.


Last Update: 12/2/2009


About the author
MSSQLTips author Ashish Kumar Mehta
Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, December 02, 2009 - 11:01:33 AM - Patrick Wood Read The Tip

Thank you for including this in your tips.  I ran across this problem a few weeks ago and somehow, after quite a few frustrating attempts, found a way to get it done.  I have since forgotten how I did it so your tip is now safely saved where I can find it when I need it the next time.


Thursday, December 03, 2009 - 7:16:17 PM - stittdba Read The Tip

If xp_cmdshell was not already enabled on your server, you will want to make sure that you disable it again as part of your clean up from this task.

 

stittdba


Thursday, December 10, 2009 - 9:18:52 AM - Toby White Read The Tip

This was the way you had to do it on SQL 2000 with the exception of the syntax being different for the attach and detach commands. The easiest way to do this in SQL Server 2005 is just change the physical file names while the database is still online, take the database offline, and then move the physical files and/or change the names, and then set the database back online:

Alter Database DatabaseName set online/offline

Regards


Friday, December 11, 2009 - 2:05:31 AM - racoonracer Read The Tip

I usually do the following, not sure if it is best practice, but it works fine for me.

1. Detach the database.

2. Rename physical database file

3. Open Query Analyzer (use Master)

4. Type in below to attach the new database name

EXEC sp_attach_db @dbname = N'<new_db_name>',
@filename1 = N'<path to database>\<new_db_name>.mdf',
@filename2 = N'<path to log>\<new_db_name>.ldf'

 5. Change the logical names, file size, growth, etc. in the GUI. (not too strong with sql commands, but I can always update this)


Thursday, July 05, 2012 - 11:24:35 AM - Miguel Quintana Read The Tip

I know this is a very old thread... I was just looking for the Syntax of  sp_renameDB, when I stumbled upon this great article.

Just one small addition. You do not really need to DETACH the database. You can get by with SET OFFLINE.

 


ALTER DATABASE [CoreDB] SET OFFLINE
-- Now rename the physical files
-- Now rename the physical files
-- Now rename the physical files

ALTER DATABASE [CoreDB] SET ONLINE


Tuesday, November 27, 2012 - 8:44:08 AM - Simon Doubt Read The Tip

Great step-by-step. Thanks very much!


Friday, November 30, 2012 - 3:17:13 PM - Mario Read The Tip

I have need to switch two databases, so I made two renames.

But views from some third DB are still reference old DB regardles name (becouse they are compiled with ID), I know if I go to view and alter it (without any real change) it will reference right DB (by name).

Question is, is there way to "rebuild" all views in all DB?

 

regards


Thursday, September 05, 2013 - 4:06:25 PM - thebrenda Read The Tip

DBCC Freeprocache will clear the cache after a database rename

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/b66354e5-2c00-4396-a8db-a657d5062266/database-rename-problem-with-views-need-help-


Monday, March 24, 2014 - 8:30:17 AM - Raj Read The Tip

good one!



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.