Hi experts,
I need to retrieve latest backup dates, looping thru a list of linked servers.
This is the code:
;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 [MyServer\MyInstnace].msdb.dbo.backupset
)
select @@SERVERNAME,
database_name,
backup_type,
backup_finish_date
from backup_cte
where rownum = 1 AND database_name NOT IN ('model')
order by database_name;
The Server name being returned is the name of the local server the statement is running on. I want the name of the server I'm retrieving backup info for.
Can this be done? Thanks!
You could try using the local servers table, something like this:
;with backup_cte as
(
select
srv.server_name,
...
from [MyServer\MyInstnace].msdb.dbo.backupset
cross join (
select name as server_name
from [MyServer\MyInstnace].master.sys.servers
where server_id = 0
) as srv
...
1 Like
Thank you very much, ScottPletcher. We're close I think.
When I run this:
;with backup_cte as
(
select
srv.server_name,
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 [AAHSSQL1401P.biz.es.svm.com\ROBOHELP_PRD].msdb.dbo.backupset
cross join (
select name as server_name
from [AAHSSQL1401P.biz.es.svm.com\ROBOHELP_PRD].master.sys.servers
where server_id = 0
) as srv
)
select
--[srv.server_name],
database_name,
backup_type,
backup_finish_date
from backup_cte
where rownum = 1 AND database_name NOT IN ('model')
order by database_name;
it returns db name etc.
BUT when I activate this line
[srv.server_name],
I get Invalid column name 'srv.server_name'.
Any ideas? Thanks again
server_name is coming from the cte, not its original srv table. Therefore, just reference it like any other column from the cte:
...
select
server_name,
database_name,
backup_type,
backup_finish_date
from backup_cte
where rownum = 1 AND database_name NOT IN (‘model’)
order by database_name;
1 Like