last backup date query

  • Great, thanks for all your advice on my queries John, Steve and Gilamonster wish me luck! 🙂

  • 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

  • You can run John Mitchell's query on a Server Group (right click on folder icon > New Query) within your Registered Servers.

  • 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