Rename a SQL Server 2008 Database

-- ============

-- USAGE NOTES

-- Best to run each Block of GO statements individually so that if there are any errors, debugging is easy.

-- ============

-- Use the logical name returned for the database

SELECT *, name AS LogicalName, physical_name AS CurrentLocation, state_desc AS StateDesc

FROM sys.master_files

WHERE database_id = DB_ID(N'<OLD_DataBase_Name>');

USE MASTER

-- Set the RESTRICT ACCESS property to SINGLE_USER in order to obtain exclusive lock

ALTER DATABASE <OLD_DataBase_Name>

SET SINGLE_USER

GO

-- Set the DB OFFLINE so we can change the MDF and LDF files

ALTER DATABASE <OLD_DataBase_Name> SET OFFLINE

GO

-- Change the name of the 'pointer' to the MDF and LDF files...note that this doesn't actually physically rename the files at the Operating System level

ALTER DATABASE <OLD_DataBase_Name> MODIFY FILE ( NAME = <LogicalName>, FILENAME = 'path\<NEW_DataBase_Name>.mdf' )

GO

ALTER DATABASE <OLD_DataBase_Name> MODIFY FILE ( NAME = <LogicalName>, FILENAME = 'path\<NEW_DataBase_Name>.ldf' )

GO

-- !!! STOP !!!

-- GO PHYSICALLY RENAME THE FILES IN EXPLORER NOW !!! If you don't, setting the DB ONLINE will fail.

-- Set the DB back ONLINE

ALTER DATABASE <OLD_DataBase_Name> SET ONLINE

GO

-- Change the name of the database

ALTER DATABASE <OLD_DataBase_Name>

MODIFY NAME = <NEW_DataBase_Name>

GO

-- Set the RESTRICT ACCESS property back to the default MULTI_USER

ALTER DATABASE <NEW_DataBase_Name>

SET MULTI_USER

GO

-- Ensure that the changes have taken place correctly

SELECT *, name AS LogicalName, physical_name AS CurrentLocation, state_desc AS StateDesc

FROM sys.master_files

WHERE database_id = DB_ID(N'<NEW_DataBase_Name>');