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