Changing logical names and physical file names of SQL Server database


The easy way of renaming a SQL Server database is to open SQL Server Management Studio, select the database name on the tree on the left handside, hit F2 and type in the new name you want. It’s like renaming a file in a folder. But when you do that, the logical name and physical filenames of the database won’t change. In order to verify this, right click on the database name, select properties. Select Files sction on the left hand side. You can see the logical name and file name for data and log files on the right hand side.

Before attempting to change the names, you should take the database offline. You can do that using SQL Management Studio (right click the database and select Tasks > Take Offiline) or using the following SQL steatement.


ALTER DATABASE <em>database_name</em> SET OFFLINE

In order to change the logical name, execute the following T-SQL statement in a new query window for the database in SQL Server Management Studio. You can use the same statement for both data and log files by changing current_logical_name and new_logical_name.


ALTER DATABASE <em>database_name</em> MODIFY FILE (NAME=<em>"current_logical_name"</em>, NEWNAME=<em>"new_logical_name"</em>)

In order to change the physical file names, execute the following T-SQL statement. You can use the same statement for both data and log files by changing the logical_name and new_file_name.


ALTER DATABASE <em>database name</em> MODIFY FILE (NAME=<em>"logical_name"</em>, FILENAME=<em>"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\new_file_name_you_want.mdf")</em>

The data and log file location may change depending upon your SQL Server installation.

After executing the SQL statements and before bringing back the database online, go ahead and change the file names for data and log files (.mdf and .ldf) to new_file_name in the file system. Now you are good to go. Go ahead and bring the database online.

You can do that using SQL Management Studio (right click the database and select Tasks > Bring Online) or using the following SQL steatement.


ALTER DATABASE <em>database_name</em> SET ONLINE

Advertisements

7 thoughts on “Changing logical names and physical file names of SQL Server database

  1. I have tried for months to look for instruction on how to change a database name. Other posts would show you how to change logical name and/or the “attached as” name, but not physical name. This is the only post the give the complete instruction. Good post. Thanks

  2. Be sure that when you try to change the PHYSICAL log file name (in the catalog) using the TSQL that you also change the LOGICAL db name to include the _log.

    One LOGICAL name is for the db, the other is for the LOG file

    MyDataBase (logical db name) MyDataBase.mdf (physical file name)
    MyDataBase_log (logical db log name) MyDataBase_log.ldf (physical file name)

  3. Pingback: Changing PerformancePoint Services 2010 Database Name « Saji Viswam's Blog

  4. I can’t change the logic name after I took the DB offline. the error message is Msg 942, Level 14, State 4, Line 1
    Database ‘DB Name’ cannot be opened because it is offline.

    Could someone tell me what I did wrong?

  5. Don’t take the database offline before attempting to alter the logical filename. You only need to take the DB offline if you’re changing the physical filenames of the files used by the database.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s