Jtdylktuy

I have this simple query that I'm creating just to test some backups ( full and diff and I'm going to improve it later. it's just a test):

------------------------------------------------------------------------------------

DECLARE @BKPPATCH_FULL VARCHAR(MAX);   --Patch do backup full

DECLARE @BKPPATCH_DIFF VARCHAR(MAX);   --Patch do backup DIFF





--CTE para backup FULL--------------------------------------------------------------

;WITH CTE_RESTORE_ROUTINE_FULL AS

(

    SELECT T1.DATABASE_NAME,

           T2.PHYSICAL_DEVICE_NAME,

           T1.backup_start_date AS 'BACKUP_DATE'

    FROM MSDB.DBO.BACKUPSET T1  

    INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY T2

        ON T1.MEDIA_SET_ID=T2.MEDIA_SET_ID

            AND T1.DATABASE_NAME='MYDATABASE'

            AND T2.PHYSICAL_DEVICE_NAME LIKE 'EXTERNAL_SERVER%'  --EXTERNO

            AND T2.PHYSICAL_DEVICE_NAME LIKE '%BKP'        --FULL

) 

SELECT TOP 1 DATABASE_NAME

            ,PHYSICAL_DEVICE_NAME 

            ,BACKUP_DATE

INTO #TEMP_NOME_E_LOCAL_BACKUP_FULL 

    FROM CTE_RESTORE_ROUTINE_FULL   

ORDER BY BACKUP_DATE DESC;



--CTE para backup DIFF--------------------------------------------------------------

;WITH CTE_RESTORE_ROUTINE_DIFF AS

(

    SELECT T1.DATABASE_NAME

          ,T2.PHYSICAL_DEVICE_NAME

          ,T1.backup_start_date AS 'BACKUP_DATE'

          ,ROW_NUMBER() OVER (ORDER BY T1.backup_start_date DESC) AS ROWNUM  

    FROM MSDB.DBO.BACKUPSET T1  

    INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY T2

        ON T1.MEDIA_SET_ID=T2.MEDIA_SET_ID

            AND T1.DATABASE_NAME='MYDATABASE'

            AND T2.PHYSICAL_DEVICE_NAME LIKE 'EXTERNAL_SERVER%'  --EXTERNO

            AND T2.PHYSICAL_DEVICE_NAME LIKE '%DIFF'       --DIFF

            AND T1.backup_start_date > CONVERT(DATE,GETDATE())

) 

SELECT DATABASE_NAME

      ,PHYSICAL_DEVICE_NAME

      ,BACKUP_DATE

      ,ROWNUM



INTO #TEMP_NOME_E_LOCAL_BACKUP_DIFF

    FROM CTE_RESTORE_ROUTINE_DIFF

        WHERE ROWNUM =  3



ORDER BY BACKUP_DATE DESC



---Insere query de RESTORE na ##TEMP_QUERY_RESTORE_FULL------------------------------------------------ 

SELECT 'RESTORE DATABASE [_'+DATABASE_NAME+'_RESTORE_TESTE'+']'+ CHAR(13) + CHAR(10) +'

            FROM DISK ='''+PHYSICAL_DEVICE_NAME+''''+ CHAR(13) + CHAR(10) +'

                WITH 

                    MOVE ''MYDATABASE'' TO ''Z:FOLDERSMYDATABASE_RESTORE_TESTE.mdf'',

                    MOVE ''MYDATABASE''TO ''Z:FOLDERSMYDATABASE_RESTORE_TESTE.ldf''WITH NORECOVERY' AS 'BKP_PATCH_FULL'



INTO #TEMP_QUERY_RESTORE_FULL

FROM #TEMP_NOME_E_LOCAL_BACKUP_FULL



--Seta Variavel para RESTORE FULL

SET @BKPPATCH_FULL = (SELECT BKP_PATCH_FULL FROM #TEMP_QUERY_RESTORE_FULL)



--Executa RESTORE FULL

EXEC (@BKPPATCH_FULL)



-----Insere query de RESTORE na ##TEMP_QUERY_RESTORE_DIFF------------------------------------------------   

SELECT 'RESTORE DATABASE [_'+DATABASE_NAME+'_RESTORE_TESTE'+']'+ CHAR(13) + CHAR(10) +'

            FROM DISK ='''+PHYSICAL_DEVICE_NAME+''''+ CHAR(13) + CHAR(10) +'

                WITH 

                    MOVE ''MYDATABASE'' TO ''Z:FOLDERSDataMYDATABASE.mdf'',

                    MOVE ''MYDATABASE''TO ''Z:FOLDERSMYDATABASE_RESTORE_TESTE.ldf''WITH RECOVERY' AS 'BKP_PATCH_DIFF'

INTO #TEMP_QUERY_RESTORE_DIFF

FROM #TEMP_NOME_E_LOCAL_BACKUP_DIFF



--Seta Variavel para RESTORE DIFF

SET @BKPPATCH_DIFF = (SELECT BKP_PATCH_DIFF FROM #TEMP_QUERY_RESTORE_DIFF)



--Executa RESTORE FULL

EXEC (@BKPPATCH_DIFF)





--Dropa Temps

DROP TABLE #TEMP_NOME_E_LOCAL_BACKUP_FULL

DROP TABLE #TEMP_QUERY_RESTORE_FULL

DROP TABLE #TEMP_NOME_E_LOCAL_BACKUP_DIFF

DROP TABLE #TEMP_QUERY_RESTORE_DIFF

If I use SELECT (@the variable) I can select the entire query to restore the full and the diff, but If I use EXEC (@The variable) It prints this error:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected) Msg 319, Level 15, State 1, Line 7 Incorrect
syntax near the keyword 'with'. If this statement is a common table
expression, an xmlnamespaces clause or a change tracking context
clause, the previous statement must be terminated with a semicolon.

(1 row(s) affected) Msg 319, Level 15, State 1, Line 7 Incorrect
syntax near the keyword 'with'. If this statement is a common table
expression, an xmlnamespaces clause or a change tracking context
clause, the previous statement must be terminated with a semicolon.

I know I can run 2 or more ctes like this:

;WITH dba1 AS(SELECT name AS SQL3, 

              ROW_NUMBER() OVER(ORDER BY name) rn 

                FROM [SERVERsql3].master.sys.databases),

      dba2 AS(SELECT name AS SQL4, 

              ROW_NUMBER() OVER(ORDER BY name) rn 

                FROM [SERVERsql4].master.sys.databases),

      dba3 AS(SELECT name AS SQL5, 

              ROW_NUMBER() OVER(ORDER BY name) rn 

                FROM [SERVERsql5].master.sys.databases)

SELECT SQL3,SQL4,SQL5 

    FROM dba1 c1

FULL JOIN dba2 c2 

    ON c1.rn = c2.rn

FULL JOIN dba3 c3

     ON c1.rn = c3.rn 

        OR c2.rn = c3.rn

But if you can see, i'm inserting data into the #temp tables.
And this is curious to me. Why it works with SELECT and not with EXEC?

The issue looks like it is on the WITH MOVE & WITH RECOVERY commands at the end, for both the full and diff restores. Chain with options together with comma's.

Original:

SELECT 'RESTORE DATABASE [_'+DATABASE_NAME+'_RESTORE_TESTE'+']'+ CHAR(13) + CHAR(10) +'

            FROM DISK ='''+PHYSICAL_DEVICE_NAME+''''+ CHAR(13) + CHAR(10) +'

                WITH 

                    MOVE ''MYDATABASE'' TO ''Z:FOLDERSMYDATABASE_RESTORE_TESTE.mdf'',

                    MOVE ''MYDATABASE''TO ''Z:FOLDERSMYDATABASE_RESTORE_TESTE.ldf''WITH NORECOVERY' AS 'BKP_PATCH_FULL'

Replace the second with with a comma:

SELECT 'RESTORE DATABASE [_'+DATABASE_NAME+'_RESTORE_TESTE'+']'+ CHAR(13) + CHAR(10) +'

            FROM DISK ='''+PHYSICAL_DEVICE_NAME+''''+ CHAR(13) + CHAR(10) +'

                WITH 

                    MOVE ''MYDATABASE'' TO ''Z:FOLDERSMYDATABASE_RESTORE_TESTE.mdf'',

                    MOVE ''MYDATABASE''TO ''Z:FOLDERSMYDATABASE_RESTORE_TESTE.ldf'', NORECOVERY' AS 'BKP_PATCH_FULL'

Same story for the diffs:

Original:

   SELECT 'RESTORE DATABASE [_'+DATABASE_NAME+'_RESTORE_TESTE'+']'+ CHAR(13) + CHAR(10) +'

                FROM DISK ='''+PHYSICAL_DEVICE_NAME+''''+ CHAR(13) + CHAR(10) +'

                    WITH 

                        MOVE ''MYDATABASE'' TO ''Z:FOLDERSDataMYDATABASE.mdf'',

                        MOVE ''MYDATABASE''TO ''Z:FOLDERSMYDATABASE_RESTORE_TESTE.ldf''WITH RECOVERY' AS 'BKP_PATCH_DIFF'

Replace the second with with a comma:

SELECT 'RESTORE DATABASE [_'+DATABASE_NAME+'_RESTORE_TESTE'+']'+ CHAR(13) + CHAR(10) +'

            FROM DISK ='''+PHYSICAL_DEVICE_NAME+''''+ CHAR(13) + CHAR(10) +'

                WITH 

                    MOVE ''MYDATABASE'' TO ''Z:FOLDERSDataMYDATABASE.mdf'',

                    MOVE ''MYDATABASE''TO ''Z:FOLDERSMYDATABASE_RESTORE_TESTE.ldf'', RECOVERY' AS 'BKP_PATCH_DIFF'

Tested by inserting the temptables one by one, and then printing the variables instead of executing:

print (@BKPPATCH_DIFF)

Result:

RESTORE DATABASE [_TEST_RESTORE_TESTE]



            FROM DISK ='C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBackuptest.bak'



                WITH 

                    MOVE 'MYDATABASE' TO 'Z:FOLDERSMYDATABASE_RESTORE_TESTE.mdf',

                    MOVE 'MYDATABASE'TO 'Z:FOLDERSMYDATABASE_RESTORE_TESTE.ldf'WITH NORECOVERY

--> Gives a syntax error.

Changed this to:

RESTORE DATABASE [_TEST_RESTORE_TESTE]



            FROM DISK ='C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBackuptest.bak'



                WITH 

                    MOVE 'MYDATABASE' TO 'Z:FOLDERSMYDATABASE_RESTORE_TESTE.mdf',

                    MOVE 'MYDATABASE'TO 'Z:FOLDERSMYDATABASE_RESTORE_TESTE.ldf' , NORECOVERY

--> No longer a syntax error