SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Renaming a Database

By Andy Warren,

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

Total article views: 9603 | Views in the last 30 days: 1
Related Articles

Rename Database

Programmatically rename a database.


Rename a database

renaming a database.


Rename a database

Renaming a database isn’t something you do frequently but it does have bit of a gotcha. First of al...


Rename a SQL Server database

This script allows you to rename a database.


Common table in two databases/servers - Replication

Common table in two databases/servers - Replication