We have a database which has been setup mirror on it, and we need to change the logical file name for data and log file. As usually, I rename the logical name by SSMS on the primary server(SQL01), but after failover to the mirror(SQL02) server, I found only the data file has correct logical name, but the log file still has the original name, it didn't change!
so I tried to change the logical name for log file by SSMS again on SQL02 which is the current primary server, but failed and got error message:
Although the logical name of log file is old name, but the error indicted the old logical name didn't exist!
so I run the query blow
select * from sysfiles
it showed the new logical name for the log file, however, when I run the query below:
select * from sys.master_files
it showed the old logical name! That's the difference~!
Table Sysfiles is in the user database, while sys.master_files is in the master database
http://msdn.microsoft.com/en-us/library/ms186782.aspx
When I changed the logical name for data and log file on Primary, the transaction of the mirrored database is transferred to mirror, however master is not sync. so in the sys.master_files, it still has the old logical name. unfortunately, SSMS get the file logical name info from sys.master_files, so we saw the old logical name in SSMS on mirror(SQL02), and we can not change the name in SSMS since it still use the old name which does not exist!
In order to correct the file logical name on mirror(SQL02), we need to update the file logical name in the master db. we rename the logical name to a temp name by "ALTER DATABASE" command, then change it back new name.
USE [DatabaseName]
GO
ALTER DATABASE [DatabaseName] MODIFY FILE (NAME=N'New_logical_name', NEWNAME=N'New_logical_name_Temp')
GO
ALTER DATABASE [DatabaseName] MODIFY FILE (NAME=N'New_logical_name_Temp', NEWNAME=N'New_logical_name')
GO
By this way, we update the logical file name in master database with new logical file name. Now primary and mirror server have the same logical file name.
There is another question:
Why the data logical name is correct in SSMS on mirror server(SQL02). I made another test
1. change the file logical name on Primary(SQL01)
2. check the logical name in table sys.master_files on mirror(SQL02), it is still the old name
3. failover to mirror(SQL02), then check the logical name in sys.master_files. this time the data file logical name has been changed to new name, while the log file logical name is still the old one.
so when mirror failover, sql server will bring the database online on mirror , and update the logical file name of data file, but there is issue, it not update the logical file name in master db.
