Mixing
2 CTES to insert into a different #temp , and then executing a variable
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: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?
sql-server-2008-r2
asked Jan 15 at 13:18
Racer SQLRacer SQL
3,02542460
add a comment |
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: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?
sql-server-2008-r2
asked Jan 15 at 13:18
Racer SQLRacer SQL
3,02542460
3
When you ranSELECT @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?
– Aaron Bertrand♦
Jan 15 at 13:31
Rather than recreate this wheel, why not use sp_DatabaseRestore?
– Erik Darling
Jan 15 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 at 13:49
add a comment |
1
1
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: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?
sql-server-2008-r2
asked Jan 15 at 13:18
Racer SQLRacer SQL
3,02542460
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?
sql-server-2008-r2
sql-server-2008-r2
asked Jan 15 at 13:18
Racer SQLRacer SQL
3,02542460
asked Jan 15 at 13:18
Racer SQLRacer SQL
3,02542460
asked Jan 15 at 13:18
Racer SQLRacer SQL
3,02542460
asked Jan 15 at 13:18
Racer SQLRacer SQL
3,02542460
asked Jan 15 at 13:18
Racer SQLRacer SQL
3,02542460
3,02542460
3
When you ranSELECT @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?
– Aaron Bertrand♦
Jan 15 at 13:31
Rather than recreate this wheel, why not use sp_DatabaseRestore?
– Erik Darling
Jan 15 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 at 13:49
add a comment |
3
When you ranSELECT @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?
– Aaron Bertrand♦
Jan 15 at 13:31
Rather than recreate this wheel, why not use sp_DatabaseRestore?
– Erik Darling
Jan 15 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 at 13:49
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?– Aaron Bertrand♦
Jan 15 at 13:31
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?– Aaron Bertrand♦
Jan 15 at 13:31
Rather than recreate this wheel, why not use sp_DatabaseRestore?
– Erik Darling
Jan 15 at 13:45
Rather than recreate this wheel, why not use sp_DatabaseRestore?
– Erik Darling
Jan 15 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 at 13:49
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 at 13:49
add a comment |
1 Answer
1
active
oldest
votes
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: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
answered Jan 15 at 13:29
Randi VertongenRandi Vertongen
2,711721
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 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!
– Randi Vertongen
Jan 15 at 13:49
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227185%2f2-ctes-to-insert-into-a-different-temp-and-then-executing-a-variable%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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: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
answered Jan 15 at 13:29
Randi VertongenRandi Vertongen
2,711721
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 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!
– Randi Vertongen
Jan 15 at 13:49
add a comment |
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: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
answered Jan 15 at 13:29
Randi VertongenRandi Vertongen
2,711721
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 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!
– Randi Vertongen
Jan 15 at 13:49
add a comment |
3
3
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: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
answered Jan 15 at 13:29
Randi VertongenRandi Vertongen
2,711721
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
answered Jan 15 at 13:29
Randi VertongenRandi Vertongen
2,711721
edited Jan 15 at 13:35
answered Jan 15 at 13:29
Randi VertongenRandi Vertongen
2,711721
answered Jan 15 at 13:29
Randi VertongenRandi Vertongen
2,711721
answered Jan 15 at 13:29
Randi VertongenRandi Vertongen
2,711721
2,711721
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 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!
– Randi Vertongen
Jan 15 at 13:49
add a comment |
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 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!
– Randi Vertongen
Jan 15 at 13:49
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 at 13:48
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 at 13:48
2
2
I tunnel visioned on the CTE for a good while too, only when printing the statement was when the penny dropped. No problem!
– Randi Vertongen
Jan 15 at 13:49
I tunnel visioned on the CTE for a good while too, only when printing the statement was when the penny dropped. No problem!
– Randi Vertongen
Jan 15 at 13:49
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227185%2f2-ctes-to-insert-into-a-different-temp-and-then-executing-a-variable%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
When you ran
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?– Aaron Bertrand♦
Jan 15 at 13:31
Rather than recreate this wheel, why not use sp_DatabaseRestore?
– Erik Darling
Jan 15 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 at 13:49