We are taking the example on SAP system copy / refresh activity. So, whenever we restore the SQL Server database from Production (PRD) into Testing system (TST). The logical name in Testing system will contain the Production system. You can use below query to check,
SELECT file_id, name as logical_name, physical_name
FROM sys.database_files
The output – the logical name is still using the Production system and the physical name is already with Testing system. The same output can be obtain using the Microsoft SQL Server Management Studio.
file_id logical_name physical_name
1 PRDDATA1 F:\TSTDATA\TSTDATA_1.mdf
2 PRDDATA2 F:\TSTDATA\TSTDATA_2.ndf
3 PRDDATA3 F:\TSTDATA\TSTDATA_3.ndf
4 PRDDATA4 F:\TSTDATA\TSTDATA_4.ndf
5 PRDDATA5 F:\TSTDATA\TSTDATA_5.ndf
6 PRDLOG2 K:\TSTLOG\TSTLOG_2.ldf
7 PRDLOG1 K:\TSTLOG\TSTLOG_1.ldf
For SAP system, you can check them in transaction code DBACOCKPIT > SPACE > OVERVIEW > FILES.
Solution
Use Microsoft SQL Server Management Studio, right click on Database and select Properties. Select Files and from here, you can edit and change the Logical Name from (example) PRDDATA1 to TSTDATA1. Click OK to apply the changes.
Alternatively, use below command to perform the changes.
USE [SID]
GO
ALTER DATABASE [SID] MODIFY FILE (NAME=N'OLDSIDDATA1', NEWNAME=N'NEWSIDDATA1')
ALTER DATABASE [SID] MODIFY FILE (NAME=N'OLDSIDDATA2', NEWNAME=N'NEWSIDDATA2')
ALTER DATABASE [SID] MODIFY FILE (NAME=N'OLDSIDDATA3', NEWNAME=N'NEWSIDDATA3')
ALTER DATABASE [SID] MODIFY FILE (NAME=N'OLDSIDDATA4', NEWNAME=N'NEWSIDDATA4')
ALTER DATABASE [SID] MODIFY FILE (NAME=N'OLDSIDDATA5', NEWNAME=N'NEWSIDDATA5')
ALTER DATABASE [SID] MODIFY FILE (NAME=N'OLDSIDLOG1', NEWNAME=N'NEWSIDLOG1')
ALTER DATABASE [SID] MODIFY FILE (NAME=N'OLDSIDLOG2', NEWNAME=N'NEWSIDLOG2')
GO
