SQL - How to rename a DATABASE physically in MS SQL Server 2008

As you know sp_renamedb stored procedure does not rename the database data files. I prefer to have the database data files contain a matching database name. If I have a database named [myDataBase], then the data files would typically be myDataBase.mdf and myDataBase_Log.ldf, simple and neat. In this way, just with take a look at the files, I can remember which SQL database they are talking about. Otherwise it's confusing.

SQL 2008 allows us to rename a database using T-SQL and the sp_renamedb stored procedure has been deprecated. ALTER DATABASE can do the job for us.

You can code as following to do the job:

1- Change your database name:

ALTER DATABASE oldDataBase
MODIFY NAME = newDataBase
GO

2- Rename master data file (.mdf):

ALTER DATABASE AutoMac
MODIFY FILE(NAME = 'oldDataBase', NEWNAME = 'newDataBase' )
GO

3- Rename log file:

ALTER DATABASE AutoMac
MODIFY FILE(NAME = 'oldDataBase_Log', NEWNAME = 'newDataBase_Log' )
GO

4- Assign new full path file name to renamed master data file:

ALTER DATABASE AutoMac
MODIFY FILE (NAME='newDataBase', FILENAME='C:\mySQLfiles\newDataBase.mdf')
GO

5- Assign new full path file name to renamed log file:

ALTER DATABASE AutoMac
MODIFY FILE (NAME='newDataBase_Log', FILENAME='C:\mySQLfiles\newDataBase_Log.ldf')
GO

6- Now you need to take the database offline (Tasks->Take Offline) and bring it back online (Tasks->Bring Online), then you don't need to detach and attach your database again.

SQL - How to rename a DATABASE physically in MS SQL Server 2008