Applies to: SQL Server 2000, SQL Server 2005 (Yukon)

It’s pretty often I see the need for a database to be renames. Sometimes its because a developer has given their database a very generic name, or sometimes becuase the use of the database has shifted.

Renaming a database is a pretty easy task (make sure you have renamed the physical files to reflect the new database name).

Restoring a database will also see it using the original logical filenames.

Most often however, people forget to change the logical filename for their database files. While this doesn’t have any real implications, it can cause some confusion.

Say for example you create a database Flowers_Dev02 by restoring a backup of Flowers_Dev01. If you were to run select * from sysfiles on both databases, you might get something like:

Flowers_Dev01:
fileid ... name                filename
------     ---------------------- -------------------
1          Flowers_Dev01_Data   f:/Flowers_Dev01.mdf
2          Flowers_Dev01_Log    f:/Flowers_Dev01.ldf

Flowers_Dev02:
fileid ... name                filename
------     ---------------------- -------------------
1          Flowers_Dev01_Data   f:/Flowers_Dev02.mdf
2          Flowers_Dev01_Log    f:/Flowers_Dev02.ldf

Renaming the logical file name is a simple task, and doesn’t require any down time of the database. The syntax of the t-sql command is simply:

alter database database_name modify file (name = current_logical_name, newname = new_logical_name)

So in our case we would simply run the following to fix our logical database file names:

alter database Flowers_Dev02 modify file (name = Flowers_Dev01_Data, newname = Flowers_Dev02_Data)
alter database Flowers_Dev02 modify file (name = Flowers_Dev01_Log, newname = Flowers_Dev02_Log)

Tags: SQL Server, T-SQL