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

добавлено: 27 окт 11
понравилось:0
просмотров: 5449
комментов: 1

теги:

Автор: 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 версии, создают дополнительную нагрузку на сервер из-за сбора информации о фактически используемом пространстве в файлах данных.
Вы пользуетесь данными скриптами на свой страх и риск.
Автор скриптов не несет никаких обязательств, связанных с прямым либо косвенным ущербом (включая лишение премии и увольнение и другие дискриминационные меры со стороны работодателя или заказчика) а также упущенной выгодой, связанной с применением данных скриптов. ;)

Комментарии


  • Позанудствую :))
    Скрипт "Версия 2005 и старше" в чистом виде на 2005 работать не будет - ну, не умеет 2005 дефолтные значения переменным присваивать, да и столбца compressed_backup_size в табличке msdb.dbo.backupset у него нет.



Необходимо войти на сайт, чтобы оставлять комментарии