There
are many ways to rename the SQL server Database name. Today, I will explain how
to rename a Database in SQL Server using T-SQL along with its Logical and
Physical Name.
The
very easiest and most popular way of renaming is – Suppose, you have a database
named as “TestDB”. Now you want to
rename it with “NewTestDB”.
--Rename the data
base "TestDB to New_TestDB".
EXEC sp_renameDB 'TestDB','New_TestDB'
GO
Above
given example just renames the Database title (name); However if you want to
rename the physical file, just Detach
the database, Go to the file path which is currently located, rename both the LDF
and MDF file, and now here Attach
the database.
Below
steps are given for renaming the database by using the TSQL statement – Just
open SSMS Copy the below scripts and press F5.
USE TestDB
GO
è First
of all set the database for a single user, it will disconnect all the currently
connected users.
ALTER DATABASE TestDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
è Now
rename the Database title (Name).
EXEC master..sp_renamedb 'TestDB','NewTestDB'
GO
è Now
the change the Logical file name, because above statement just change the name
of the Database.
ALTER DATABASE NewTestDB MODIFY
FILE (NAME=N'TestDB', NEWNAME=N'NewTestDB')
GO
ALTER DATABASE NewTestDB MODIFY
FILE (NAME=N'TestDB_log', NEWNAME=N'NewTestDB_log')
GO
è Now
Detach the Database for change the Physical file name, as we can't do anything
with the physical file when the database is in active mode.
EXEC master.dbo.sp_detach_db @dbname = N'NewTestDB'
GO
è Now
Change the SSMS settings if you haven’t set it.
è This
is necessary because the below provided command will not run without set this.
sp_configure 'show advanced options'
GO
/* 0 = Disabled
, 1 = Enabled */
sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE WITH OVERRIDE
GO
è Now
rename your physical files with the use of command.
EXEC xp_cmdshell 'RENAME "F:\CD\Temporary Work\TestDB.mdf", "NewTestDB.mdf"'
GO
EXEC xp_cmdshell 'RENAME "F:\CD\Temporary Work\TestDB_log.ldf",
"NewTestDB_log.ldf"'
GO
è Now
just attach the database. (Currently detached by us).
CREATE DATABASE NewTestDB ON
( FILENAME = N'F:\CD\Temporary Work\NewTestDB.mdf' ),
( FILENAME = N'F:\CD\Temporary Work\NewTestDB_log.ldf' )
FOR ATTACH
GO
è Now
set the database as multi users can work on this.
ALTER DATABASE NewTestDB SET
MULTI_USER
GO
è Finally
you can see that, the physical files are renamed.
SELECT
name AS
[Logical Name],
physical_name AS
[DB File Path],
type_desc AS
[File Type],
state_desc AS
[State]
FROM
sys.master_files
WHERE
database_id =
DB_ID(N'NewTestDB')
GO
Conclusion
Hence,
we have learnt how we can rename the SQL Server Database along with its Logical
& Physical using some simple TSQL statements.
This is by far the clearest explanation out there. Nice job.
ReplyDeleteThanks
Delete