It seems to be erroring on the line:
when ''I'' then ''differential''
Declare @Servername nvarchar(50);
DECLARE @SQL nvarchar(200);
Set @Servername = 'MyServerName\PRODUCT_PRICE'
SET @SQL = N';with backup_cte as (select
database_name,
backup_type = case type,
when ''D'' then ''database''
when ''L'' then ''log''
when ''I'' then ''differential''
else ''other''
end,
backup_finish_date,
rownum =
row_number() over
(
partition by database_name, type
order by backup_finish_date desc
)
from ' + @ServerName + '.msdb.dbo.backupset)'
Gives errors:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string 'different'.
SET @SQL = N';with backup_cte as (select
database_name,
backup_type = case backup_type
when 'D' then 'database'
when 'L' then 'log'
when 'I' then 'differential'
else 'other'
end,
backup_finish_date,
rownum =
row_number() over
(
partition by database_name, type
order by backup_finish_date desc
)
from ' + @ServerName + '.msdb.dbo.backupset)'
EXEC sys.sp_executesql @sql;
... but I stlll get this (confusing) error:
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'D'.