Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Best way to rename a database

Author  Topic 

Razzle00
Starting Member

35 Posts

Posted - 2012-12-20 : 13:44:35
Hi,

I have a database with over a 100 tables, stored procedures and views. I would like to rename the data to something more descriptive to the project. Can someone tell me the best practice to do this so that all of the tables, stored procedures and views and the Logical Name and Physical File Name database is updated as well. I found this article on the internet, but was not sure if this is 100% correct. Thanks in advance!

http://www.mssqltips.com/sqlservertip/1891/best-practice-for-renaming-a-sql-server-database/

Razzle

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-20 : 14:00:21
looks good. can you afford downtime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Razzle00
Starting Member

35 Posts

Posted - 2012-12-20 : 14:13:41
Yes, I can schedule downtime. Just need to make sure that the rename works without cause any problems with identity, permissions and to be sure backups will be able reference the newly named database.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-12-20 : 14:49:20
>>"...so that all of the tables, stored procedures and views..."

I'm confused by this. Are all your objects named with the database name as part of the table/sp/view name? The link doesn't address that.

Be One with the Optimizer
TG
Go to Top of Page

Razzle00
Starting Member

35 Posts

Posted - 2012-12-20 : 15:03:20
TG,

No, the tables, stored procedures and views are not named with the database name. What I meant was, for example, every stored procedure when you look at the code, reference the database name at the beginning. Like this..

USE [MyDataBaseName]
GO
/****** Object: StoredProcedure [dbo].[MyTableName] Script Date: 12/20/2012 2:58:53 PM ******/

would the USE [MyDataBaseName] be renamed as well in the stored procedure code? That is what I am refering to.

Thanks,

Razzle
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-20 : 16:26:25
Those will change based on Databasename but if you have used USE DB inside the code then you will have to change it or if you are using three part naming convention
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-12-20 : 16:43:22
You do need to consider any cross database references in existing code. processes outside the database that rely on connection strings, SQLCMD or OSQL script files, source control code, etc.

I have found that changing the the name of existing objects is just not worth it. It's hard enough on new project that has been under way for a little while but on projects that are deployed, in use and been around can be a nightmare. Especially if it is just a matter of not being descriptive enough.

I've lived with some stupid names. And that is why the worst part of my job when I have to come up with new object names. It can take me longer to decide on one stupid column name than it takes me to code half the app

Be One with the Optimizer
TG
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2012-12-21 : 17:46:47
No, the article is certainly not the best approach to use to rename a db.

In particular, you do NOT want to detach a db; that's an obsolete method of renaming it. Instead, you make the needed changes in the master db using ALTER commands; there are examples in Books Online. Then take the db OFFLINE and back ONLINE to put the changes into affect.

The "USE <db_name>" when you look at a stored proc are generated at that time, so a db name change won't be any issue there.

However, any cross-database references you have, or any 3-part names within the db code, will NOT automatically be changed. You need to idenity and change all those yourself.
Go to Top of Page
   

- Advertisement -