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 (https://blog.sqlauthority.com)
16 Comments. Leave new
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?
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.
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
Thanks It is working fine. I am able to rename Physical file also. Thanku very much
Thanks for confirmation Vishal.
Oops!!
There’s a 7th step to above comment:
EXEC sp_dboption NewDB, ‘Single User’, FALSE
how to create rename database on runtime through procedure
@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.
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
Sir
I want rename the mdf and ldf files using store procedure with simple way
Hi there,
i want to execute trigger after renaming database, do you have any idea how to perform this.?
the sp_dboption does not exists in sql server 2012…
Yeah. ALTER DATABASE need to be used.
Rename SQL Server Database using T-SQL. Following commands works for SQL Server 2005, 2008, 2008R2, 2012, 2014 and 2016:
ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE DBName MODIFY NAME = DBName_New
ALTER DATABASE DBName_New SET MULTI_USER WITH ROLLBACK IMMEDIATE
Hi. Is there a way to re-name all logical names in a server at once with cursor? Such as re-naming all database logical names from “DatabaseName__PRIMARY__01” to “PRIMARY__01”?