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?