DB Advice 2

[robertfah]

We've got a program that is about to merge 3 others into 1. The current DB name is unique to the original program and no longer makes sense to use, so we are thinking of changing it. Question is, how easy is it to change a DB name or is it easier to make a full backup of it and then create a NEW DB with the proper name and then do a restore on it but point to the old DB backup? Won't log file names have to change at that point too?

Thanks!

 

[robertfah]

Sorry, forgot to mention we use SQL 2000.

 

[ousoonerjoe]

Since you are using 2000, I would recommend just creating a back up and restoring the backup directly to the new instance you wish to name the database. Then drop the old version.

Be careful of any procedures that make explicit calls to the old database name. Those can really jump up and bite you.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[gmmastros]

There is a built in function specifically for this: sp_rename

http://msdn.microsoft.com/en-us/library/ms188351.aspx

[tt][blue]
sp_rename 'OldDatabaseName','NewDatabaseName','DATABASE'
[/blue][/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[robertfah]

Thanks everyone. What about SQL 2008, will the sp_rename work? I didn't think it existsed in 2008.

I just realized that the staging DB is 2008 while our production is 2000.

 

[ousoonerjoe]

I just verified it in 2008 R2. The odds are in your favor that it is in 2008.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[robertfah]

Thanks a bunch!

 

[gmmastros]

With SQL2005 and newer, you can use the Alter Database command.

You can test this by creating a new database and then re-naming it, like this:

Create Database [Tek-Tips]

Alter Database [Tek-Tips] Modify Name = [LessThanDot]

Verify this works. To remove the database...

Drop Database [LessThanDot]

Your original question mentioned SQL2000 which is why I recommended sp_rename. For sql2005+, it's (probably) better to use the Alter Database statement.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Check

http://beyondrelational.com/justlea...ame-databasenametablename-and-columnname.aspx

and comments to it.

PluralSight Learning Library