Просмотр списка баз на сервере и пути последних бэкапов SQL Server

http://www.sql.ru/blogs/decolores/1067

добавлено: 27 окт 11

Автор: DeColo®es

Как-то, для систематизации бэкапов баз, потребовалось составить список баз с информацией о том, когда и куда был сделан последний бэкап, текущем размере базы и т.д.

Собственно, делюсь скриптами.
Скрипты разные для версии 2000 и всех от 2005 и старше.
Тот, который написан для 2000 конечно будет работать и на 2005+, но он медленнее из-за необходимости обновления информации о реальном распределении пространства внутри файла данных.

Версия 2005 и старше:

if object_id('tempdb..#dbs') is not null drop table #dbs

create table #dbs(

Name varchar(128) primary key,

Mode varchar(20),

DataSize int,

FreeSpace int,

LogSize int,

LastBackupTime datetime,

LastBackupFile varchar(1000),

LastBackupSize int,

LastBackupRatio money,

LastLogBackupTime datetime

)

exec sp_msforeachdb 'use ?

declare @db_name varchar(128) = db_name()

if @db_name not in(''tempdb'', ''model'')

begin

        print db_name()

        declare

               @Used int,

               @DBID int = db_id(),

               @Mode varchar(20),

               @DataSize int,

               @LogSize int,

               @LastBackupTime datetime,

               @LastLogBackupTime datetime,

               @LastBackupFile varchar(1000),

               @LastBackupSize int,

               @LastBackupRatio money

        select 

               @Mode = recovery_model_desc

        from sys.databases 

        where database_id = @DBID

        select

                @DataSize    = sum(case when type = 0 then size else 0 end)/128,

               @LogSize = sum(case when type = 1 then size else 0 end)/128

        from sys.database_files

        select

               @Used = sum(page_count)/128.

        from sys.dm_db_index_physical_stats(@DBID, null,null, null, null) 

        select 

               top 1

               @LastBackupTime = bs.backup_finish_date,

               @LastBackupSize = isnull(bs.compressed_backup_size, bs.backup_size)/1024,

               @LastBackupRatio = isnull(bs.backup_size*1./bs.compressed_backup_size, 1),

               @LastBackupFile = mf.physical_device_name

        from msdb.dbo.backupset bs

        cross apply(

               select top 1 *

               from msdb.dbo.backupmediafamily mf

               where mf.media_set_id = bs.media_set_id

               order by mf.family_sequence_number

        ) mf

        where bs.database_name = @db_name

        and type = ''D''

        order by bs.backup_finish_date desc   

        select 

               top 1

               @LastLogBackupTime = bs.backup_finish_date

        from msdb.dbo.backupset bs

        cross apply(

               select top 1 *

               from msdb.dbo.backupmediafamily mf

               where mf.media_set_id = bs.media_set_id

               order by mf.family_sequence_number

        ) mf

        where bs.database_name = @db_name

        and type = ''L''

        order by bs.backup_finish_date desc   

        insert into #dbs(

               Name,          Mode,          DataSize,              FreeSpace,             LogSize,               LastBackupTime,        LastBackupFile,        LastBackupSize,         LastBackupRatio,               LastLogBackupTime              )

        select 

               @db_name,              @Mode,         @DataSize,             @DataSize - @Used,         @LogSize,              @LastBackupTime,               @LastBackupFile,               @LastBackupSize,               @LastBackupRatio,              @LastLogBackupTime

end'

select * from #dbs

Для версии 2000:

if object_id('tempdb..#dbs') is not null drop table #dbs

create table #dbs(

Name varchar(128) primary key,

Mode varchar(20),

DataSize int,

FreeSpace int,

LogSize int,

LastBackupTime datetime,

LastBackupFile varchar(1000),

LastBackupSize int,

LastBackupRatio money,

LastLogBackupTime datetime

)

exec sp_msforeachdb 'use ?

if db_name() not in(''tempdb'', ''model'')

begin

        print db_name()

        declare

               @Used int,

               @DBID int,

               @db_name varchar(128),

               @Mode varchar(20),

               @DataSize int,

               @LogSize int,

               @LastBackupTime datetime,

               @LastLogBackupTime datetime,

               @LastBackupFile varchar(1000),

               @LastBackupSize int,

               @LastBackupRatio money

        select 

               @Mode = convert(varchar(20),  databasepropertyex(name, ''Recovery'')),

               @DBID = dbid,

               @db_name = name

        from master.dbo.sysdatabases 

        where dbid = db_id()

        select

               @DataSize    = sum(case when groupid > 0 then size else 0 end)/128,

               @LogSize = sum(case when groupid = 0 then size else 0 end)/128

        from sysfiles

        dbcc updateusage(0);

        select

               @Used = sum(reserved)/128.

        from sysindexes

        where indid in(0,1)

        select 

               top 1

               @LastBackupTime = bs.backup_finish_date,

               @LastBackupSize = bs.backup_size/1024,

               @LastBackupFile = mf.physical_device_name

        from msdb.dbo.backupset bs

        inner join msdb.dbo.backupmediafamily mf

               on mf.media_set_id = bs.media_set_id

        where bs.database_name = @db_name

        and type = ''D''

        order by bs.backup_finish_date desc   ,

               mf.family_sequence_number

        select 

               top 1

               @LastLogBackupTime = bs.backup_finish_date

        from msdb.dbo.backupset bs

        inner join msdb.dbo.backupmediafamily mf

               on mf.media_set_id = bs.media_set_id

        where bs.database_name = @db_name

        and type = ''L''

        order by bs.backup_finish_date desc   ,

               mf.family_sequence_number

        insert into #dbs(

               Name,           Mode,          DataSize,              FreeSpace,             LogSize,               LastBackupTime,        LastBackupFile,        LastBackupSize,         LastBackupRatio,               LastLogBackupTime              )

        select 

               @db_name,              @Mode,         @DataSize,             @DataSize - @Used,         @LogSize,              @LastBackupTime,               @LastBackupFile,               @LastBackupSize,               @LastBackupRatio,              @LastLogBackupTime

end'

select * from #dbs

Если нужно собрать информации одновременно с нескольких серверов, делаем так:
1. В SSMS, в дереве Registered Servers выбирам группу серверов, для которых нужно выполнить скрипт.
(Я для подобных целей создал несколько групп, разделяя сервера по разным признакам - версия, "важность" и т.д.)
2. В контекстном меню выбираем New Query
3. Выполняем запрос. Для каждой возвращенной строки будет добавлен столбец с именем сервера.

DISCLAMER

Скрипты, особенно 2000 версии, создают дополнительную нагрузку на сервер из-за сбора информации о фактически используемом пространстве в файлах данных. Вы пользуетесь данными скриптами на свой страх и риск. Автор скриптов не несет никаких обязательств, связанных с прямым либо косвенным ущербом (включая лишение премии и увольнение и другие дискриминационные меры со стороны работодателя или заказчика) а также упущенной выгодой, связанной с применением данных скриптов. ;)