Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
2  
You're moving the same thing ('@BackupName') twice –  Remus Rusanu Dec 3 '12 at 15:17
    
Isnt backup name supposed to be the name of the backup database? Or do I need to specify that it is the backup databases mdf and ldf files respectively? –  Lumpy Dec 3 '12 at 15:20
    
You move the logical file (by name). Not the backup name, nor the database name. You retrieve the name of the file(s) in the backup using RESTORE FILELISTONLY –  Remus Rusanu Dec 3 '12 at 15:31

1 Answer 1

up vote 5 down vote accepted

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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.