Changing a Database File Logical Name

Database Design

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:

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

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)

  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Blogter
  • BlogMemes
  • Yahoo Buzz

Changing a Database File Logical Name | 0 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.

Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 Copyright © 2018 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.