SQLServerCentral Article

Renaming a Database

,

It's not often you'll need to rename a database, but when you do what looks

like a trivial task can be complicated in a hurry. Today we'll review the

techniques used to rename a database and discuss some of the more interesting

problems that can arise from renaming a database.

So why rename a database? I'd bet the most common reason is to fix a typo, or

to remove an embedded space (spaces in object names are a pain). Perhaps less

common would be to align the database name with a naming convention. Probably

less common still is if something in the database name changes. For example, at

point I worked for a company where each of our clients had their own database.

It could be very confusing to tell someone to do something for customer ACME but

that the data is stored in SPROCKET. In SQL 2000 it was possible to update

sysdatabases directly to fix something like incorrect casing (Sprocket instead

of SPROCKET) but that is not allowed in SQL 2005. If the database has been in

use for a while the impact of renaming the database can be substantial, or at

least seem that way! I'd suggest doing it early in the development cycle before

dependences start to accrue.

But if you need to do it, what's involved? I've started by creating a test database called June 2008. To get started

using Management Studio, select the database, right click, select properties,

and then rename (you can also select the database and press F2).

I'll enter a name of 'July2008' and press enter. If no one else is accessing

the database the rename operation will succeed, but otherwise you'll get this

error:

The same thing can happen if we do it the 'old school' way using sp_renamedb:

As of SQL 2005 sp_renamedb is deprecated, the preferred technique is to use

alter database, which gives us the same error:

The answer of course is to kick out all the users that are accessing the

database and keep the out long enough to make the change. Typically this is done

by looping through non system spids and issuing a KILL on each spid, and then

trying to set DBO only, or just immediately trying the rename again. On a busy

system it's often easier to just yank the network cable! SQL 2005 includes the

option to append 'ROLLBACK IMMEDIATE' to the alter, but it doesn't work on the

rename operation:

But what you can do is make it a multi step process that leverages the new

functionality:

So that finally worked and makes one of the most common obstacles in renaming

a database easy to overcome. What are the other, more complicated hurdles? Let's

take a look:

  • A little SQL trivia, you can't rename a database while a backup

    operation is in progress

  • You will need to revise any connection strings that reference the

    database directly (applications, reporting services, etc)

  • If you're using the name of the database in three part syntax (select *

    from dbname.dbo.sometable) or four part syntax (select * from

    servername.dbname.dbo.sometable) in code it will need to be updated (don't

    forget to check jobs that may be using this technique). Note also that this

    includes views & synonyms used to abstract the connection to this renamed

    database within other databases.

  • Linked servers (a form of a connection string)
  • Maintenance plans if you aren't using 'all databases' or 'all user

    databases'

  • Source control will need to be updated to match (unless you want a clean

    break to start fresh)

All of those are fixable, but don't expect to find and fix 100% on the first

try. Odds are fair that you'll miss one or two obscure places and temporarily

have some breakage. The hardest to fix are those in code that requires

recompilation (and perhaps re-testing).

I think obviously it makes sense to get the name right to start with. But if

it needs to be changed, now you know how along some of the risk points.

Visit my blog at

http://blogs.sqlservercentral.com/andy_warren/default.aspx

Rate

4.26 (35)

You rated this post out of 5. Change rating

Share

Share

Rate

4.26 (35)

You rated this post out of 5. Change rating