Multiple Bak files restore for migration

Question

We have this old server we need to migrate to new server.

We have a script that will backup all the databases to folder C:\SQLBackup.

I've already migrated several newer servers with no issue

DECLARE @datapath VARCHAR(512)
Set @Datapath = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\DATA\'

SELECT 'use master; ' + 'Restore Database ' + '[' + DBName + ']' + ' From Disk =' + ''''+ 'C:SQLBackup\' + DBName + '.bak' +'''' + ' WITH  FILE = 1, MOVE N'+ DBName +'_DATA' + ' TO '' @datapath'' + DBname + '_DATA.mdf', MOVE N'+ DBName +'_LOG' TO @datapath' + DBName + '_LOG.ldf',  NOUNLOAD,  STATS = 5' ';'
FROM
    (SELECT db.name AS DBName
     FROM sys.databases db
     WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')) D

Thanks

I get an error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TO'.

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'D'

Answer 1

Please don't consider this an answer, this is not tested to make sure it's generating anything valid. Its only meant to show what's wrong with your concatenation syntax.

DECLARE @Datapath VARCHAR(512);
SET @Datapath = 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\DATA\';
SELECT 'use master; ' + 'Restore Database ' + '[' + DBName + ']' + ' From Disk =' + ''''+ 'C:SQLBackup\' + DBName + '.bak' +'''' + ' WITH  FILE = 1, MOVE N'+ DBName +'_DATA' + ' TO ' + @Datapath + DBname + '_DATA.mdf, MOVE N'+ DBName +'_LOG TO ' + @datapath + DBName + '_LOG.ldf,  NOUNLOAD,  STATS = 5;'
FROM (
    SELECT
        db.name AS DBName
    FROM sys.databases db
    WHERE name NOT IN ('master','model','msdb','tempdb')
) D