如何在服务器上提取数据库详细信息 [英] How to pull out database details on server

问题描述

如何从我们的服务器2012获取以下信息？

How do I get the following information from our server 2012?

我需要知道以下内容：

数据库列表名称，

每个当前大小，

高可用性（HA），b
RPO（恢复点目标），

RTO（恢复时间目标），

当前备份计划，

估计增长因子，

估计所需大小（以GB为单位）。

List of database names,
Current size of each,
High availability (HA),
RPO (Recovery Point Objectives),
RTO (Recovery Time Objectives),
Current backup schedule,
Estimated Growth Factor,
Estimate size required (in GB).

上述情况可能吗？

谢谢，

推荐答案

数据库名称列表，  ;

从sysdatabases中选择名称

select name from sysdatabases

将表中的备份详细信息保留在作业中。这将用于容量报告。

keep backup details in table trough job. This will used for capacity report.

SET NOCOUNT ON        
SET ANSI_WARNINGS OFF      
 
      
select d.name as database_name,
b.type,
CONVERT(numeric(9,2),b.backup_size/1048576)as 'Dbsize_in_MB',
b.backup_start_date,b.backup_finish_date,
datediff(mi,b.backup_start_date,b.backup_finish_date)as 'Time_taken(Min)',
 m.physical_device_name as physical_location,
datediff(d, b.backup_finish_date, getdate()) as 'dayssincelast'
into #backupcheck
from  master..sysdatabases d with (nolock)
left join msdb..backupset b  with (nolock) on d.name = b.database_name 
left join msdb..backupmediafamily m  with (nolock) on m.media_set_id = b.media_set_id 
where d.name not in ('tempdb','pubs','northwind') and b.type='D'
and b.backup_start_date = (select max(backup_start_date) 
                                   from msdb..backupset b2 
                                   where b.database_name = b2.database_name
                                  and b2.type = 'D')
      
select d.name as database_name,
b.type,
CONVERT(numeric(9,2),b.backup_size/1048576)as 'Dbsize_in_MB',
b.backup_start_date,b.backup_finish_date,
datediff(mi,b.backup_start_date,b.backup_finish_date)as 'Time_taken(Min)',
 m.physical_device_name as physical_location,
datediff(d, b.backup_finish_date, getdate()) as 'dayssincelast'
into #backupcheck1
from  master..sysdatabases d with (nolock)
left join msdb..backupset b  with (nolock) on d.name = b.database_name 
left join msdb..backupmediafamily m  with (nolock) on m.media_set_id = b.media_set_id 
where d.name not in ('tempdb','pubs','northwind') and b.type='L'
and b.backup_start_date = (select max(backup_start_date) 
                                   from msdb..backupset b2 
                                   where b.database_name = b2.database_name
                                  and b2.type = 'L')
        
select d.name as database_name,
b.type,
CONVERT(numeric(9,2),b.backup_size/1048576)as 'Dbsize_in_MB',
b.backup_start_date,b.backup_finish_date,
datediff(mi,b.backup_start_date,b.backup_finish_date)as 'Time_taken(Min)',
 m.physical_device_name as physical_location,
datediff(d, b.backup_finish_date, getdate()) as 'dayssincelast'
into #backupcheck2
from  master..sysdatabases d with (nolock)
left join msdb..backupset b  with (nolock) on d.name = b.database_name 
left join msdb..backupmediafamily m  with (nolock) on m.media_set_id = b.media_set_id 
where d.name not in ('tempdb','pubs','northwind') and b.type='I'
and b.backup_start_date = (select max(backup_start_date) 
                                   from msdb..backupset b2 
                                   where b.database_name = b2.database_name
                                  and b2.type = 'I')        
                                 
        
select   a.name,
DATABASEPROPERTYEX(a.name,'recovery')as recovery_model,
case b.type   
when 'L' then 'LOG BACKUP SCHEDULED'
when 'D' then 'Full BACKUP SCHEDULED'
when 'I' then 'DIFF BACKUP SCHEDULED'
else 'NO FULL BACKUP SCHEDULED'
end type , 
b.backup_finish_date,
dayssincelast,
physical_location,
Dbsize_in_MB
into #backupcheck_final from master..sysdatabases a
left outer join #backupcheck  b on a.name=b.database_name  
where a.name not in(
'tempdb')

--and dayssincelast is null

select   a.name,
DATABASEPROPERTYEX(a.name,'recovery')as recovery_model,
case b.type   
when 'L' then 'LOG BACKUP SCHEDULED'
when 'D' then 'Full BACKUP SCHEDULED'
when 'I' then 'DIFF BACKUP SCHEDULED'
else 'NO LOG BACKUP SCHEDULED'
end type ,
b.backup_finish_date,
dayssincelast,
physical_location,
Dbsize_in_MB
into #backupcheck_final1 
 from master..sysdatabases a
left outer join #backupcheck1  b on a.name=b.database_name 
where a.name not in ('tempdb','pubs','northwind','model','master','msdb')  
--and dayssincelast is null
delete from #backupcheck_final where type ='NO LOG BACKUP SCHEDULED' and recovery_model ='simple'
delete from #backupcheck_final1 where type ='NO LOG BACKUP SCHEDULED' and recovery_model ='simple'

select   a.name,
DATABASEPROPERTYEX(a.name,'recovery')as recovery_model,
case b.type   
when 'L' then 'LOG BACKUP SCHEDULED'
when 'D' then 'Full BACKUP SCHEDULED'
when 'I' then 'DIFF BACKUP SCHEDULED'
else 'NO LOG BACKUP SCHEDULED'
end type ,
b.backup_finish_date,
dayssincelast,
physical_location,
Dbsize_in_MB
into #backupcheck_final2 
 from master..sysdatabases a
left outer join #backupcheck2  b on a.name=b.database_name 
where a.name not in ('tempdb','pubs','northwind','model','master','msdb')
delete from #backupcheck_final2 where type ='NO LOG BACKUP SCHEDULED'  
--and dayssincelast is null

--create table #backupcheck_final3 (name varchar(250),recovery_model varchar (50),type varchar(50),backup_finish_date datetime,dayssincelast int,physical_location varchar(max),Dbsize_in_MB int)
select * into #backupcheck_final3 from #backupcheck_final
insert into #backupcheck_final3 select * from #backupcheck_final1
insert into #backupcheck_final3 select * from #backupcheck_final2

if (select COUNT(1) from #backupcheck_final3 where type='DIFF BACKUP SCHEDULED' and dayssincelast =0)>0
begin
delete #backupcheck_final3 where (type='FULL BACKUP SCHEDULED' and dayssincelast < 7)
end

select * FROM #backupcheck_final3

drop table #backupcheck
drop table #backupcheck1
drop table #backupcheck2
drop table #backupcheck_final
drop table #backupcheck_final1
drop table #backupcheck_final2
drop table #backupcheck_final3

数据库大小： -

select d.name,sum(s.size * 8/1024) as size_MB from sysaltfiles s,sysdatabases d where s.dbid=d.dbid
group by d.name
order by size_MB desc

始终是synsc状态

SELECT 
	ar.replica_server_name, 
	adc.database_name, 
	ag.name AS ag_name, 
	drs.is_local, 
	drs.is_primary_replica, 
	drs.synchronization_state_desc, 
	--drs.is_commit_participant, 
	drs.synchronization_health_desc, 
	--drs.recovery_lsn, 
	--drs.truncation_lsn, 
	--drs.last_sent_lsn, 
	--drs.last_sent_time, 
	--drs.last_received_lsn, 
	--drs.last_received_time, 
	--drs.last_hardened_lsn, 
	--drs.last_hardened_time, 
	--drs.last_redone_lsn, 
	--drs.last_redone_time, 
	--drs.log_send_queue_size, 
	--drs.log_send_rate, 
	--drs.redo_queue_size, 
	--drs.redo_rate, 
	--drs.filestream_send_rate, 
	--drs.end_of_log_lsn, 
	--drs.last_commit_lsn, 
	drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc 
	ON drs.group_id = adc.group_id AND 
	drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
	ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar 
	ON drs.group_id = ar.group_id AND 
	drs.replica_id = ar.replica_id
ORDER BY 
	ag.name, 
	ar.replica_server_name, 
	adc.database_name;

这篇关于如何在服务器上提取数据库详细信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！