Skip to content

How To: Rename SQL Server 2005 Database Using TSQL

July 10, 2008

For some reasons, DBA’s need to rename their Databases. One of the reasons might be to comply to Best Practices Policy changes.

Here we are going to rename our Database named “Bad_Ass_DB” into “Best_Practice_DB”. This is how Database renaming with TSQL is done in SQL Server 2005:

First we need to put our Bad_Ass_DB offline or alter it to single-user mode. You cannot rename a database if it is online or in multi-user mode. And prior to that, there should be no active connection to the database (user, application, etc):

 

  1. USE master
  2. ALTER DATABASE Bad_Ass_DB
  3. SET single_user
  4. GO

Now we can rename the Database:

  1. ALTER DATABASE Bad_Ass_DB
  2. MODIFY NAME = Best_Practice_DB
  3. GO

Then we can set the database back to multi-user mode. Then, real hard work begins: mapping all your codes, processess, applications, web.config, etc to the new name.

  1. ALTER DATABASE Best_Practice_DB
  2. SET multi_user
  3. GO

 

-Marlon Ribunal 
kick it on DotNetKicks.com

ON The News: SQL Server 2008 On Productivity Boosts

About these ads
2 Comments leave one →
  1. NAS permalink
    January 25, 2010 4:41 AM

    hello Marlon, i change the name of the database , but de file still the same how can i change the filename??? thank’s

Trackbacks

  1. Database Management » Blog Archive » How To: Rename SQL Server 2005 Database Using TSQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives



  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video


  • Software Blogs - BlogCatalog Blog Directory
  • Follow

    Get every new post delivered to your Inbox.

    Join 28 other followers

    %d bloggers like this: