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?
SELECT @cmdit worked because you're just selecting a string, not executing it.SELECT 'what now?'works too, even thoughEXEC('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?