mssql 2000+ show backup list

MSSQL Скрипт просмотра всех бэкапов определенной базы данных, работает на MSSQL 2000 + И выше



    declare @cmt varchar(200)
    set @cmt = ''

    declare @dbname varchar(250)
    set @dbname = 'db_name'

    print @cmt+'database '+@dbname

    print @cmt+'full backup of '+@dbname
    declare @full_bs_id        numeric(38)
    declare @full_start        datetime
    declare @full_end        datetime
    declare @full_created    bit
    declare @full_lsn        numeric(38)
    declare @full_media_id    numeric(38)
    declare @full_media_file varchar(2000)
    declare @full_media_pos numeric(38)

    set @full_created = 0

    select top 1
        @full_created = 1,
        @full_bs_id = backup_set_id,
        @full_start = backup_start_date,
        @full_end   = backup_finish_date,
        @full_lsn   = database_backup_lsn,
        @full_media_pos = position,
        @full_media_id = media_set_id,
        @full_media_file = (select b.physical_device_name from msdb.dbo.backupmediafamily b where b.media_set_id = bs.media_set_id)
    from msdb.dbo.backupset bs
    where
        bs.database_name = @dbname
        and bs.type = 'D'
    order by bs.backup_start_date desc

    if @full_created = 1 begin
        print @cmt+'full backupset id:       '+cast(@full_bs_id as varchar)
        print @cmt+'full backupset started:  '+convert(varchar(200),@full_start,120) -- +cast(@full_start as varchar)
        print @cmt+'full backupset finished: '+convert(varchar(200),@full_end,120) -- +cast(@full_end as varchar)
        print @cmt+'full backupset db lsn:   '+cast(@full_lsn as varchar)
        print @cmt+'full backupset media id: '+cast(@full_media_id as varchar)
        print @cmt+'full media file:         '+@full_media_file
        print @cmt+'full media position:     '+cast(@full_media_pos as varchar)
       
        print @cmt+'diff backup of '+@dbname
        declare @diff_bs_id numeric(38)
        declare @diff_start datetime
        declare @diff_end   datetime
        declare @diff_created bit
        declare @diff_lsn   numeric(38)
        declare @diff_media_id    numeric(38)
        declare @diff_media_file varchar(2000)
        declare @diff_media_pos numeric(38)

        set @diff_created = 0

        select top 1
            @diff_created = 1,
            @diff_bs_id = backup_set_id,
            @diff_start = backup_start_date,
            @diff_end   = backup_finish_date,
            @diff_lsn   = database_backup_lsn,
            @diff_media_pos = position,
            @diff_media_id = media_set_id,
            @diff_media_file = (select b.physical_device_name from msdb.dbo.backupmediafamily b where b.media_set_id = bs.media_set_id)
        from msdb.dbo.backupset bs
        where
            bs.database_name = @dbname
            and bs.type = 'I'
            and bs.database_backup_lsn > @full_lsn
        order by bs.backup_start_date desc

        if @diff_created = 1 begin
            print @cmt+'diff backupset id:       '+cast(@diff_bs_id as varchar)
            print @cmt+'diff backupset started:  '+convert(varchar(200),@diff_start,120) -- +cast(@diff_start as varchar)
            print @cmt+'diff backupset finished: '+convert(varchar(200),@diff_end,120) -- +cast(@diff_end as varchar)
            print @cmt+'diff backupset db lsn:   '+cast(@diff_lsn as varchar)
            print @cmt+'diff backupset media id: '+cast(@diff_media_id as varchar)
            print @cmt+'diff media file:         '+@diff_media_file
            print @cmt+'diff media position:     '+cast(@diff_media_pos as varchar)
        end
    end else begin
        print @cmt+'! full backup of '+@dbname+' not exists'
    end

    go