SQL SERVER – FIX: Msg 4353 – Conflicting File Relocations Have Been Specified for File. Only a Single WITH MOVE Clause Should be Specified for Any Logical File Name

This was one of an interesting error which I received while preparing for a demo. I wanted to demonstrate a disaster recovery scenario by doing backup and restore. In this blog we would learn how to fix Msg 4353 – Conflicting file relocations have been specified for the file. Only a single WITH MOVE clause should be specified for any logical file name.

To reproduce this error, we need to create a database which has two files which the same “logical” name and different physical name. Creating that is also a tricky and for me, it was created due to my copy/paste mistake. If you or your developers are lazy like me, you can run into this error.

Before running below, make sure C: has a folder called Temp.

CREATE DATABASE [SQLAuthority]
ON PRIMARY
(NAME = 'SQLAuthority', FILENAME = 'C:\Temp\SQLAuthority.mdf')
LOG ON
(NAME = 'SQLAuthority_log', FILENAME = 'C:\Temp\SQLAuthority_log.ldf')
GO

Now, I am adding 2 data files and 2 log files.

ALTER DATABASE SQLAuthority ADD FILE
(NAME = 'SQLAuthority_D1', FILENAME = 'C:\Temp\D1.ndf'),
(NAME = 'SQLAuthority_D1', FILENAME = 'C:\Temp\D2.ndf')
GO
ALTER DATABASE SQLAuthority ADD LOG FILE
(NAME = 'SQLAuthority_L1', FILENAME = 'C:\Temp\L1.ndf'),
(NAME = 'SQLAuthority_L1', FILENAME = 'C:\Temp\L2.ndf')

Do you notice any problem in above? Yeah, two physical files having a same logical name.

Solarwinds

I took a backup of this database as below.

BACKUP DATABASE SQLAuthority TO DISK = 'C:\Temp\SQLAuthority.bak'

Here is the output

Processed 328 pages for database ‘SQLAuthority’, file ‘SQLAuthority’ on file 1.
Processed 8 pages for database ‘SQLAuthority’, file ‘SQLAuthority_D1’ on file 1.
Processed 8 pages for database ‘SQLAuthority’, file ‘SQLAuthority_D1’ on file 1.
Processed 3 pages for database ‘SQLAuthority’, file ‘SQLAuthority_log’ on file 1.
Processed 0 pages for database ‘SQLAuthority’, file ‘SQLAuthority_L1’ on file 1.
Processed 0 pages for database ‘SQLAuthority’, file ‘SQLAuthority_L1’ on file 1.
BACKUP DATABASE successfully processed 347 pages in 0.050 seconds (54.140 MB/sec).

You can clearly see that backup is happening for all the files, including duplicates. Now, I wanted to restore the database on a different server. Since there was no C:\Temp on the destination server, I decided to relocate the files to a different folder, so I used UI to restore it. Here is the error which I got.

SQL SERVER - FIX: Msg 4353 - Conflicting File Relocations Have Been Specified for File. Only a Single WITH MOVE Clause Should be Specified for Any Logical File Name conflict-err-01

Conflicting File Story

When I looked at T-SQL, here is the one which is failing

USE [master]
RESTORE DATABASE [SQLAuthority]
FROM DISK = N'D:\SQLAuthority.bak'
WITH FILE = 1
,MOVE N'SQLAuthority' TO N'F:\DATA\SQLAuthority.mdf'
,MOVE N'SQLAuthority_D1' TO N'F:\DATA\D1.ndf'
,MOVE N'SQLAuthority_D1' TO N'F:\DATA\D2.ndf'
,MOVE N'SQLAuthority_log' TO N'F:\LOG\SQLAuthority_log.ldf'
,MOVE N'SQLAuthority_L1' TO N'F:\LOG\L1.ndf'
,MOVE N'SQLAuthority_L1' TO N'F:\LOG\L2.ndf'
,NOUNLOAD
,STATS = 5
GO

As we can see, we have the same logical name twice in the restore statement.

WORKAROUND/SOLUTION

The error message is saying a pretty obvious thing but why did it allow me to do that in the first place? ALTER command which I ran, in the beginning, should have shown the error message to me about conflicting files. However, there is no error for the conflicting files.

Anyways, the workaround would be to create the same path as the source on this server. Basically, we need to avoid MOVE command and let it restore files at the same location where they wanted to go.

SQL SERVER - FIX: Msg 4353 - Conflicting File Relocations Have Been Specified for File. Only a Single WITH MOVE Clause Should be Specified for Any Logical File Name conflict-err-02

Another workaround would be to rename the logical file name using below command.

USE [SQLAuthority]
GO
ALTER DATABASE [SQLAuthority] MODIFY FILE (NAME=N'SQLAuthority_D1', NEWNAME=N'SQLAuthority_D2')
GO
USE [SQLAuthority]
GO
ALTER DATABASE [SQLAuthority] MODIFY FILE (NAME=N'SQLAuthority_L1', NEWNAME=N'SQLAuthority_L2')
GO

Interestingly, the above command changes name only for the one file (out of two in this case). Once renamed then another backup is needed to be restored.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Fix Error – Login failed for user. Reason: Server is in script upgrade mode. Only administrator can connect at this time. (Microsoft SQL Server, Error: 18401)
Next Post
SQL SERVER – REBUILDDATABASE Error: 0x851A0012 – Missing sa Account Password. The sa Account Password is Required for SQL Authentication Mode

Related Posts

Leave a Reply

Menu