1

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:FOLDERS\MYDATABASE_RESTORE_TESTE.mdf'',
                    MOVE ''MYDATABASE''TO ''Z:FOLDERS\MYDATABASE_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:FOLDERS\Data\MYDATABASE.mdf'',
                    MOVE ''MYDATABASE''TO ''Z:FOLDERS\MYDATABASE_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 [SERVER\sql3].master.sys.databases),
      dba2 AS(SELECT name AS SQL4, 
              ROW_NUMBER() OVER(ORDER BY name) rn 
                FROM [SERVER\sql4].master.sys.databases),
      dba3 AS(SELECT name AS SQL5, 
              ROW_NUMBER() OVER(ORDER BY name) rn 
                FROM [SERVER\sql5].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?

3
  • 3
    When you ran SELECT @cmd it worked because you're just selecting a string, not executing it. SELECT 'what now?' works too, even though EXEC('what now?') would not. Did you look at the string when you ran the select instead of the exec? Could you put that in the question? Jan 15 '19 at 13:31
  • Rather than recreate this wheel, why not use sp_DatabaseRestore? Jan 15 '19 at 13:45
  • Thanks Aaron. there was one more WITH. Oh thanks Erik, I didn't know about this sp. I will read about it. Thanks bros.
    – Racer SQL
    Jan 15 '19 at 13:49
3

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:FOLDERS\MYDATABASE_RESTORE_TESTE.mdf'',
                    MOVE ''MYDATABASE''TO ''Z:FOLDERS\MYDATABASE_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:FOLDERS\MYDATABASE_RESTORE_TESTE.mdf'',
                    MOVE ''MYDATABASE''TO ''Z:FOLDERS\MYDATABASE_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:FOLDERS\Data\MYDATABASE.mdf'',
                        MOVE ''MYDATABASE''TO ''Z:FOLDERS\MYDATABASE_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:FOLDERS\Data\MYDATABASE.mdf'',
                    MOVE ''MYDATABASE''TO ''Z:FOLDERS\MYDATABASE_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 Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\test.bak'

                WITH 
                    MOVE 'MYDATABASE' TO 'Z:FOLDERS\MYDATABASE_RESTORE_TESTE.mdf',
                    MOVE 'MYDATABASE'TO 'Z:FOLDERS\MYDATABASE_RESTORE_TESTE.ldf'WITH NORECOVERY

--> Gives a syntax error.

Changed this to:

RESTORE DATABASE [_TEST_RESTORE_TESTE]

            FROM DISK ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\test.bak'

                WITH 
                    MOVE 'MYDATABASE' TO 'Z:FOLDERS\MYDATABASE_RESTORE_TESTE.mdf',
                    MOVE 'MYDATABASE'TO 'Z:FOLDERS\MYDATABASE_RESTORE_TESTE.ldf' , NORECOVERY

--> No longer a syntax error

2
  • 2
    God dammit the momment I saw Aarons's comment I knew I did something wrong. I look at this exactly statement. I was really focused trying to fix the CTE i didn't even notice the sitax was wrong. thanks guys !
    – Racer SQL
    Jan 15 '19 at 13:48
  • 2
    I tunnel visioned on the CTE for a good while too, only when printing the statement was when the penny dropped. No problem! Jan 15 '19 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.