Sql-server – Only a single WITH MOVE clause should be specified for any logical file name

backupsql serversql-server-2008stored-procedures

I'm trying to do a copy database by scripting a backup and restore. I have the backup procedure working correctly but I keep getting the error

Conflicting file relocations have been specified for file. Only a
single WITH MOVE clause should be specified for any logical file name.

when I run the procedure to do the restore.

Here is the procedure code. Can anyone spot what my error is?

ALTER PROCEDURE spDBRestore (@BackupName NVARCHAR(50), @RestoreName NVARCHAR(50))
AS 
BEGIN
DECLARE @BackupPath NVARCHAR(4000) = 'S:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\TemplateCopy\'+@BackupName+'.BAK',
@NewDBFileName NVARCHAR(4000) = 'S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'+@RestoreName+'.mdf',
@NewLFFileName NVARCHAR(4000) = 'L:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\'+@RestoreName+'_1.ldf'
select @BackupName, @RestoreName, @NewDBFileName, @newlffilename
RESTORE DATABASE @RestoreName
FROM DISK = @BackupPath
WITH FILE = 1,
MOVE @BackupName TO @NewDBFileName,
MOVE @BackupName TO @NewLFFileName,
NOUNLOAD,
REPLACE;

END

Best Answer

You shouldn't be moving the backup file, the MOVE clause takes the logical database file name as the first parameter, and then the TO ... is the destination physical filename for the corresponding database file.

BOL reference on RESTORE

MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ ...n ]

Specifies that the data or log file whose logical name is specified by logical_file_name_in_backup should be moved by restoring it to the location specified by operating_system_file_name. The logical file name of a data or log file in a backup set matches its logical name in the database when the backup set was created.

You can retrieve the logical file name of the database files from RESTORE FILELISTONLY (note, the LogicalName column in the result set).

Related Question