SQL SERVER – Rename Database to New Name Using Stored Procedure by Changing to Single User Mode

In my organization we rename the database on development server when are refreshing the development server with live data. We save the old database with new name and restore the database from live with same name. If developer/Jr. DBA have not saved the SQL Script from development server, he/she can go back to old Server and retrieve the script.

There are few interesting facts to note when the database is renamed.

  • When renamed the database, filegroup name or filename (.mdf,.ldf) are not changed.
  • User with SA privilege can rename the database with following script when the context of the database is master database.

USE master;
GO
EXEC sp_dboption AdventureWorks, 'Single User', True
GO
EXEC sp_renamedb 'AdventureWorks', 'AdventureWorks_New'
GO
EXEC sp_dboption AdventureWorks, 'Single User', False
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

12 thoughts on “SQL SERVER – Rename Database to New Name Using Stored Procedure by Changing to Single User Mode

  1. I tried this on my development machine with a test database, and it didn’t rename the .mdf/.ldf files. Did your post mean to say that it would rename those files?

    Like

  2. No, he said, it wouldn’t.

    There are few interesting facts to note when the database is renamed.

    When renamed the database, filegroup name or filename (.mdf,.ldf) are not changed.

    Like

  3. There is a mistake in the code above:

    After the rename has successfully occurred, this line:

    EXEC sp_dboption AdventureWorks, ‘Single User’, False

    will not work as stated because the DB name:
    AdventureWorks will no longer exist…

    The proper T-SQL line should be:
    EXEC sp_dboption AdventureWorks_New, ‘Single User’, False

    (from this page:

    http://blog.sqlauthority.com/2007/09/19/sql-server-rename-database-to-new-name-using-stored-procedure-by-changing-to-single-user-mode/

    )

    –Rich G. from Stratford, CT

    Like

  4. In response to the above comment to completely rename a SQL Server 2005 database, including the operating system files, the following steps should be carefully executed while connected as an administrator.

    Do not be connected to DB to be renamed… run these commands either from OSQL or while connected to a different DB.

    For this example, the DB name ‘OldDB’ is your current database name with the operating system filenames ‘Old-DB_Data.mdf’ and ‘Old-DB_Log.ldf’. The newly desired database name is ‘NewDB’ with the operating system filenames ‘New-DB_dat.mdf’ and ‘New-DB_log.ldf’

    — Six Steps to completely renaming a Database:
    ————————————————

    — 1. If you’re using some SQL Server DB management software that “remembers” your temp queries and/or environment based on the currently registered DB (e.g. EMS), connect to the desired DB and backup whatever you need.

    — 2. Always look around before you touch:
    EXEC sp_databases — to discover all attached DBs
    GO
    EXEC sp_helpdb ‘OldDB’ — specifics for to-be-renamed DB
    GO
    — Review the 2nd resultset for the LOGICAL database name
    — and the LOGICAL log name. For this example we’ll use:
    — ‘Old_DB_dat’ and Old_DB_log’, respectively.
    — They’re not used here, but beneficial to be aware of.

    — 3. Prevent others from connecting while you’re doing it:
    EXEC sp_dboption OldDB, ‘Single User’, TRUE
    GO
    — Do it! (rename at the DB level… o/s rename at Step 5):
    ALTER DATABASE OldDB MODIFY NAME = NewDB
    — or the following, in place of ALTER DATABASE above:
    EXEC sp_renamedb ‘OldDB’, ‘NewDB’
    GO

    — 4. Detach the database from SQL Server:
    EXEC sp_detach_db @dbname = ‘NewDB’
    GO

    — 5. Rename and/or Move the operating system files at command prompt.
    — example… rename AND move files:
    — C:
    — cd \SQL_Stuff
    — REN Old-DB_Data.mdf New-DB_dat.mdf
    — REN Old-DB_Log.ldf New-DB_log.ldf
    — MOVE New-DB_???.?df C:\SQL_Databases

    — 6. Attach the newly-renamed & moved database files:
    EXEC sp_attach_db @dbname = ‘NewDB’
    , @filename1 = ‘C:\SQL_Databases\New-DB_dat.mdf’
    , @filename2 = ‘C:\SQL_Databases\New-DB_log.ldf’
    GO

    Like

  5. @neeraj,

    Just put these statements in your stored procedure.

    SQL Server 2000
    ———————
    Use master
    Sp_renamedb ‘old_db_name’ , ‘new_db_name’

    SQL Server 2005 and Higher
    ————————————-
    ALTER DATABASE old_db_name
    MODIFY NAME = new_db_name

    Note: When you rename a database, no user should be connection to that specific database.

    ~ IM.

    Like

  6. Dude!
    Have u even tested you script?

    How will below stmt work when u have renamed the DB to _new already?

    EXEC sp_dboption AdventureWorks, ‘Single User’, False

    Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #047 | Journey to SQL Authority with Pinal Dave

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