Tuesday, August 30, 2011

Change the case of the Logical Name of a SQL Server Database

When you create a database, by default data file and log file will be added to the database. For logical name it will be, Databasename_log. Let us say you want to change it to Database_Log (where L is in upper case).

ALTER DATABASE [TailLog]
MODIFY FILE (NAME=N'TailLog_log', NEWNAME=N'TailLog_Log')
GO

Then you will get an following error.

Msg 1828, Level 16, State 3, Line 1
The logical file name "TailLog_Log" is already in use. Choose a different name.

Let us try to do this from the SSMS.

image

So basically you are not allow to do this.

What you have to do is, first set it to another name.

ALTER DATABASE [TailLog]
MODIFY FILE (NAME=N'TailLog_log', NEWNAME=N'TailLog_LogTemp')
GO

The file name 'TailLog_LogTemp' has been set.

Then change it back to TailLog_Log so that you can achieve your target.

ALTER DATABASE [TailLog]
MODIFY FILE (NAME=N'TailLog_LogTemp', NEWNAME=N'TailLog_Log')
GO

No comments:

Post a Comment