Renaming a SQL Server database’s logical name

Pete Sral

At times we copy a SQL Server database from one database name to another for testing or development.  One thing that doesn’t change is the logical name:

Using the following SQL script the logical name can be renamed:

-- data file
ALTER DATABASE [NetTraxNet_Dev_v3.1]
MODIFY FILE (NAME=[NetTraxNet_Dev_v3.0], NEWNAME=[NetTraxNet_Dev_v3.1])

-- log file
ALTER DATABASE [NetTraxNet_Dev_v3.1]
MODIFY FILE (NAME=[NetTraxNet_Dev_v3.0_log], NEWNAME=[NetTraxNet_Dev_v3.1_Log])

Note the “[“ and “]” around the names, this is due to a compiler warning:

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ‘.1’.

If all goes well with the execution of the SQL, the following messages will confirm the new name:

The file name ‘NetTraxNet_Dev_v3.1’ has been set.

The file name ‘NetTraxNet_Dev_v3.1_Log’ has been set.

Checking the properties shows the updated logical name:

Why is this important… if you query some of the internal SQL Server (system) tables the logical name is used and if you have multiple databases with the same logical name can get confusing.

Update

Brad pointed out to me that you can also use SSMS (SQL Server Management Studio) to change the logical name.

  1. Right click on the database.
  2. Click on the Properties menu.
  3. In the “Select a page” list, click on Files.
  4. In the grid under Logical Name place your cursor in the cell and make your changes.
  5. Click “OK” and the change will be saved.