How to change logical name of SQL Server database?

To change the logical name of the SQL Server databse one can follow the steps below to change the logical name of database data file and log file. You may want to take a backup of the database before applying the change to ensure you have a rollback.

Example:
Use test
select fileid, name, filename from sysfiles
fileid name filename
1 test C:\MSSQL\Data\test.mdf
2 test_Log C:\MSSQL\Data\test_log.LDF

— rename data file
ALTER DATABASE Monitor MODIFY FILE (NAME = test, NEWNAME = App_Data)
GO
— rename log file
ALTER DATABASE Monitor MODIFY FILE (NAME = test_Log, NEWNAME = App_Log)
GO

— verify change
select fileid, name, filename from sysfiles
fileid name filename
1 App_Data C:\MSSQL\Data\test.mdf
2 App_Log C:\MSSQL\Data\test_log.LDF

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