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

Административные скрипты MS SQL > Просмотр списка баз на сервере и пути последних бэкапов SQL Server
05.07.2013 16:00:24


Наиболее часто встречающиеся слова в статье:

[db_name] [varchar] [ select] [DataSize] [LogSize] [LastBackupTime] [datetime] [LastBackupFile] [LastBackupSize] [LastLogBackupTime]


Статья:

Версия 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