March 24, 2011 at 6:13 am
Great, thanks for all your advice on my queries John, Steve and Gilamonster wish me luck! 🙂
June 12, 2012 at 6:15 am
Here's a version that'll run on one database and produce results for all databases (assuming they're setup as linked servers on the main db).
Could be further improved by putting the type lookup into a permanent table and outputting results of dynamic sql to a temp table from where you could feed it into whatever additional queries / functionality you wanted.
declare @server_id int
declare @sql nvarchar(max)
set @server_id = -1
while exists (select top 1 1 from sys.servers where server_id > @server_id)
begin
select top 1 @server_id = server_id, @sql = QUOTENAME(name)
from sys.servers
where server_id > @server_id
order by server_id
set @sql = 'select *
from
(
select QUOTENAME(bs.server_name) + ''.'' + QUOTENAME(bs.database_name) DBName
, bs.type BackupTypeCode --included since it looks like this field could be null, so not restricted to the lookup values
, t.descrptn BackupType
, COALESCE(Convert(nvarchar(20), backup_finish_date, 113),''Backup Not Taken'') FinishDate
, COALESCE(Convert(nvarchar(20), backup_size, 101),''NA'') Size
, COALESCE(Convert(nvarchar(20), compressed_backup_size, 101),''NA'') CompressedSize
, COALESCE(Convert(nvarchar(20), backup_set_id, 101),''NA'') SetID
, name BackupName
, ROW_NUMBER() over (partition by bs.database_name, bs.type order by bs.backup_start_date desc) r
from ' + @sql + '.msdb.dbo.backupset bs
left outer join
(
select ''D'' id, ''Database'' descrptn
union select ''I'' ,''Differential database''
union select ''L'' ,''Log''
union select ''F'' ,''File or filegroup''
union select ''G'' ,''Differential file''
union select ''P'' ,''Partial''
union select ''Q'' ,''Differential partial''
) t on bs.type = t.id
) x where r = 1
order by DBName, BackupTypeCode
'
exec (@sql) --you could insert to a temp table / something like that to make use of the results later
end
August 12, 2014 at 3:04 am
You can run John Mitchell's query on a Server Group (right click on folder icon > New Query) within your Registered Servers.
August 12, 2014 at 3:23 am
Yes, that'll work if you want to run it interactively. If you want to automate it, you'll need to centralise using SSIS or similar.
John
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply