I am currently working on a full restore SQL-script and I have following Problem: While restoring you have to decide where the mdf, ndf and ldf - files of the database should be stored. To achieve that I use WITH MOVE. If I insert the path by myself it works just fine.
The problem comes up when I start using string-variables instead of the actual path (for easier use). It says that there is something wrong near +, but I can't figure out what it is. So my question is, what is it?
RESTORE DATABASE Test_EMPI --name of the database
FROM DISK = @EMPIBackupFileLocation --works just fine
WITH MOVE '5_47_4403_Official_GER_EMPI' TO @RestoreFileLocation + '\' + @EMPIName + '1.mdf', --doesnt work
MOVE 'MV_TABLES' TO 'C:\Program Files\....\DATA\Test_EMPI2.NDF', --also works
...........
Executing my script I get the Error: Incorrect syntax near '+'.
Try this way
DECLARE @destination VARCHAR(1000) = @RestoreFileLocation + '\' + @EMPIName + '1.mdf'
RESTORE DATABASE Test_EMPI --name of the database
FROM DISK = @EMPIBackupFileLocation --works just fine
WITH MOVE '5_47_4403_Official_GER_EMPI' TO @destination,
MOVE 'MV_TABLES' TO 'C:\Program Files\....\DATA\Test_EMPI2.NDF', --also works