[SOLVED] Multiple Bak files restore for migration

Multiple Bak files restore for migration

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'

Solution

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