SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Best way to rename a database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Razzle00
Starting Member

35 Posts

Posted - 12/20/2012 :  13:44:35  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/20/2012 :  14:00:21  Show Profile  Reply with Quote
looks good. can you afford downtime?

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

Go to Top of Page

Razzle00
Starting Member

35 Posts

Posted - 12/20/2012 :  14:13:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/20/2012 :  14:49:20  Show Profile  Reply with Quote
>>"...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 - 12/20/2012 :  15:03:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/20/2012 :  16:26:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/20/2012 :  16:43:22  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
409 Posts

Posted - 12/21/2012 :  17:46:47  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000