IT:AD:SQL Server:HowTo:SQL:Rename a Database

The steps involved are: * Knock everybody off the line to do the work (ie, set the db to SINGLEUSER mode) * Rename the logical names * Optionally: * Detaching the database: * renaming the physical files * Reattaching. * Allowing users back on (back to MULTIUSER mode).

-- Set Database as a Single User
ALTER DATABASE SomeDb 
    SET SINGLE_USER 
    WITH ROLLBACK IMMEDIATE

-- Change Logical File Name
ALTER DATABASE [SomeDb] MODIFY FILE (NAME=N'SomeDb', NEWNAME=N'SomeNewName')
GO
ALTER DATABASE [SomeDb] MODIFY FILE (NAME=N'SomeDb_log', NEWNAME=N'SomeNewName_log')
GO

Detach the db:

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'SomeDB'
GO

At this point, rename the files manually.

When done, reattach the new db:

USE [master]
GO

CREATE DATABASE SomeNewDb ON
    ( FILENAME = N'D:\DATA\SomeNewDb.mdf' ),
    ( FILENAME = N'D:\DATA\SomeNewDb_log.ldf' )
    FOR ATTACH
GO

And finally, allow users back on:

ALTER DATABASE SomeDb 
    SET MULTI_USER
GO

• http://zarez.net/?p=1058
• http://www.mssqltips.com/sqlservertip/1891/best-practice-for-renaming-a-sql-server-database/