partitioned Database Restore issue

Question

I am having a issue for restoring a partitioned database using the below script.

I have 7 partitions in the database.

here is the code . 

declare @Sql varchar(max)

SET @Sql = 'RESTORE DATABASE [NEWS]
 FILE = ''DEMO'',
 FILEGROUP = ''DEMO''
 FROM DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\DEMO_clone.bak
 WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
RESTORE LOG [NEWS]
  FROM DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\DEMO_clone.bak''
  WITH RECOVERY
GO'''
EXEC (@Sql)

Its showing the error as incorrect syntax near '\'.

Anyone knows what is the issue is ?

Answer 1

GO is not a T-SQL command so you can't include it in a dynamic SQL script. GO is a batch separator interpreted by SQL Server client tools.

There is no need for the GO batch separators or dynamic SQL in here so I'm guessing the SQL in your question is actually built dynamically. There are also some other errors like misplaced quotes in your code. Below is the corrected version. I think you may need to add NORECOVERY to the RESTORE DATABASE too:

SET @Sql = 'RESTORE DATABASE [NEWS]
 FILE = ''DEMO'',
 FILEGROUP = ''DEMO''
 FROM DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\DEMO_clone.bak''
 WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10;
 RESTORE LOG [NEWS]
  FROM DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\DEMO_clone.bak'';
';