MS SQL de Database hakkında ve Backup durumu hakkında detaylı bilgi edinme

Merhabalar,

Bu yazım ilk olduğu için biraz kötü olmuş olabilir anlayışınız için teşekkür ederim. :)

Aşağıdaki sorguda server daki tüm databaseler hakkında detaylı bilgilere erişebiliyoruz ; databaseCollation,databaseSize,databaseEdition ve ek olarak server daki databaselerimizin backup alınıyor mu ?En son backup nezaman alınmış,kaç MB olmuş,kaç saniye sürmüş ve diğer gerekli gördüğüm kolonları ekledim.Umarım faydalı olur.

if exists(select * from sys.databases where name not in (‘master’,’tempdb’,’model’,’msdb’,’ReportServer’,’ReportServerTempDB’))
begin
SELECT @@SERVERNAME AS 
 ServerName, 
 @@SERVICENAME 
 InstanceName, 
 ( CASE 
 WHEN CONVERT(VARCHAR(128), Serverproperty (‘productversion’)) LIKE 
 ‘8%’ THEN 
 ‘SQL2000’ 
 WHEN CONVERT(VARCHAR(128), Serverproperty (‘productversion’)) LIKE 
 ‘9%’ THEN 
 ‘SQL2005’ 
 WHEN CONVERT(VARCHAR(128), Serverproperty (‘productversion’)) LIKE 
 ‘10.0%’ 
 THEN ‘SQL2008’ 
 WHEN CONVERT(VARCHAR(128), Serverproperty (‘productversion’)) LIKE 
 ‘10.5%’ 
 THEN ‘SQL2008 R2’ 
 WHEN CONVERT(VARCHAR(128), Serverproperty (‘productversion’)) LIKE 
 ‘11%’ 
 THEN 
 ‘SQL2012’ 
 WHEN CONVERT(VARCHAR(128), Serverproperty (‘productversion’)) LIKE 
 ‘12%’ 
 THEN 
 ‘SQL2014’ 
 WHEN CONVERT(VARCHAR(128), Serverproperty (‘productversion’)) LIKE 
 ‘13%’ 
 THEN 
 ‘SQL2016’ 
 WHEN CONVERT(VARCHAR(128), Serverproperty (‘productversion’)) LIKE 
 ‘14%’ 
 THEN 
 ‘SQL2017’ 
 WHEN CONVERT(VARCHAR(128), Serverproperty (‘productversion’)) LIKE 
 ‘15%’ 
 THEN 
 ‘SQL2019’ 
 ELSE ‘unknown’ 
 END ) AS 
 VersionInf, 
 CONVERT(VARCHAR(128), Serverproperty(‘ProductLevel’)) AS 
 ProductLevel, 
 CONVERT(VARCHAR(128), Serverproperty(‘Edition’)) AS Edition 
 , 
 CONVERT(VARCHAR(128), Serverproperty(‘ProductVersion’)) AS 
 ProductVersion, 
 CONVERT(VARCHAR(10), Connectionproperty(‘net_transport’)) AS 
 net_transport, 
 CONVERT(VARCHAR(10), Connectionproperty(‘protocol_type’)) AS 
 protocol_type, 
 CONVERT(VARCHAR(10), Connectionproperty(‘auth_scheme’)) AS 
 auth_scheme, 
 CONVERT(VARCHAR(30), ISNULL(Connectionproperty(‘local_net_address’),’Null’)) AS 
 local_net_address, 
 CONVERT(VARCHAR(10), ISNULL(Connectionproperty(‘local_tcp_port’),’Null’)) AS 
 local_tcp_port, 
 CONVERT(VARCHAR(30), ISNULL(Connectionproperty(‘client_net_address’),’Null’)) AS 
 client_net_address, 
 Db_name(MF.database_id) AS 
 databaseName,
 db.state_desc,
 (SELECT total_physical_memory_kb / 1024 
 FROM master.sys.dm_os_sys_memory) AS 
 ‘Physical Memory (MB)’, 
 (SELECT CONVERT(INT, value_in_use) 
 FROM master.sys.configurations 
 WHERE NAME LIKE ‘%max server memory%’) AS 
 ‘Max Server Memory’, 
 (SELECT physical_memory_in_use_kb / 1024 
 FROM master.sys.dm_os_process_memory) AS 
 ‘SQL Server Memory Usage (MB)’, 
 (SELECT available_physical_memory_kb / 1024 
 FROM master.sys.dm_os_sys_memory) AS 
 ‘Available Memory (MB)’, 
 (SELECT ‘Min. Deger =’ 
 + Cast(cfg.minimum AS NVARCHAR) 
 + ‘, Max. Deger =’ 
 + Cast(cfg.maximum AS NVARCHAR) 
 + ‘, ConfigValue=’ 
 + Cast(cfg.value AS NVARCHAR) 
 + ‘, RunValue=’ 
 + Cast(cfg.value_in_use AS NVARCHAR) 
 FROM master.sys.configurations AS cfg 
 WHERE configuration_id = ‘1538’) 
 SERVER_COST_THRESHOLD, 
 (SELECT ‘Min. Deger =’ 
 + Cast(cfg.minimum AS NVARCHAR) 
 + ‘, Max. Deger =’ 
 + Cast(cfg.maximum AS NVARCHAR) 
 + ‘, ConfigValue=’ 
 + Cast(cfg.value AS NVARCHAR) 
 + ‘, RunValue=’ 
 + Cast(cfg.value_in_use AS NVARCHAR) 
 FROM master.sys.configurations AS cfg 
 WHERE configuration_id = ‘1539’) 
 SERVER_MAX_DEGREE, 
 create_date, 
 compatibility_level, 
 collation_name, 
 user_access_desc, 
 recovery_model_desc, 
 physical_name dbsavefile 
 , 
 (size*8/1024) sizeMB, 
 max_size, 
 growth, 
 CAST(is_percent_growth AS int)is_percent_growth, 
 ISNULL(last_backup_device_name,’NULL’)last_backup_device_name, 
 ISNULL(backupsecond, 0)backupsecond, 
 ISNULL(backupsize_gb/1024/1024, 0)backupsize_MB, 
 ISNULL(is_compressed,0)is_compressed, 
 ISNULL(last_db_backup_start_date, 0)last_db_backup_start_date, 
 ISNULL(last_db_backup_finish_date,0)last_db_backup_finish_date, 
 ISNULL(backuptype, ‘NULL’)backuptype, 
 ISNULL([backup age (hours)],999) [backup age (Days)], 
 Getdate() 
 createdDate 
FROM sys.master_files AS mf 
 LEFT JOIN(SELECT 
 database_name, 
 (SELECT total_physical_memory_kb / 1024 
 FROM master.sys.dm_os_sys_memory) AS 
 ‘Physical Memory (MB)’ 
 , 
 (SELECT 
 CONVERT(INT, value_in_use) 
 FROM master.sys.configurations 
 WHERE NAME LIKE ‘%max server memory%’) AS 
 ‘Max Server Memory’ 
 , 
 (SELECT 
 physical_memory_in_use_kb / 1024 
 FROM master.sys.dm_os_process_memory) AS 
 ‘SQL Server Memory Usage (MB)’, 
 (SELECT available_physical_memory_kb / 1024 
 FROM master.sys.dm_os_sys_memory) AS 
 ‘Available Memory (MB)’, 
 (SELECT ‘Min. Deger =’ 
 + Cast(cfg.minimum AS NVARCHAR) 
 + ‘, Max. Deger =’ 
 + Cast(cfg.maximum AS NVARCHAR) 
 + ‘, ConfigValue=’ 
 + Cast(cfg.value AS NVARCHAR) 
 + ‘, RunValue=’ 
 + Cast(cfg.value_in_use AS NVARCHAR) 
 FROM master.sys.configurations AS cfg 
 WHERE configuration_id = ‘1538’) 
 SERVER_COST_THRESHOLD, 
 (SELECT ‘Min. Deger =’ 
 + Cast(cfg.minimum AS NVARCHAR) 
 + ‘, Max. Deger =’ 
 + Cast(cfg.maximum AS NVARCHAR) 
 + ‘, ConfigValue=’ 
 + Cast(cfg.value AS NVARCHAR) 
 + ‘, RunValue=’ 
 + Cast(cfg.value_in_use AS NVARCHAR) 
 FROM master.sys.configurations AS cfg 
 WHERE configuration_id = ‘1539’) 
 SERVER_MAX_DEGREE, 
 M.physical_device_name 
 last_backup_device_name, 
 QUERY.backupsecond, 
 QUERY.backupsize_gb, 
 is_compressed, 
 QUERY.backup_start_date 
 last_db_backup_Start_date, 
 QUERY.last_db_backup_date 
 last_db_backup_finish_date, 
 QUERY.backuptype, 
 [backup age (hours)], 
 Getdate() createdDate 
 FROM (SELECT database_name, 
 Max(BC.media_set_id) 
 media_set_id, 
 Max(bc.backup_finish_date) 
 AS 
 last_db_backup_date, 
 ( CASE 
 WHEN bc.type = ‘D’ THEN ‘Full_Backup’ 
 ELSE ‘Log_Backup’ 
 END ) 
 BackupType, 
 Datediff(dd, Max(bc.backup_finish_date), Getdate 
 ()) 
 AS 
 [Backup Age (Hours)], 
 Cast(Max(bc.backup_size) AS 
 bigint) 
 AS 
 BackupSize_GB, 
 Cast(Datediff(ss, Max(bc.backup_start_date), 
 Max(bc.backup_finish_date)) 
 AS 
 VARCHAR(10 
 )) 
 AS 
 BackupSecond, 
 Max(bc.backup_start_date) 
 backup_start_date 
 FROM msdb.dbo.backupset bc 
 WHERE bc.type IN ( ‘D’, ‘L’ ) 
 GROUP BY database_name, 
 bc.type) QUERY 
 JOIN msdb.dbo.backupmediafamily m 
 ON QUERY.media_set_id = m.media_set_id 
 INNER JOIN msdb.dbo.backupmediaset bm 
 ON m.media_set_id = bm.media_set_id)AS QUERY2 
 ON Db_name(mf.database_id) = QUERY2.database_name 
 AND mf.type_desc = CASE 
 WHEN( QUERY2.backuptype ) = ‘Full_Backup’ 
 THEN 
 ‘ROWS’ 
 WHEN ( QUERY2.backuptype ) = ‘Log_Backup’ 
 THEN 
 ‘LOG’ 
 END 
 INNER JOIN sys.databases AS db 
 ON db.database_id = mf.database_id and mf.database_id<>2 
end
else 
begin
select 
 @@SERVERNAME ServerName,@@SERVICENAME InstanceName, ‘YOK’ VersionInf,’YOK’ ProductLevel
,’Database Yok’Edition,’Yok’ productversion,’Yok’net_transport
,’YOK’ protocol_type,’Null’auth_scheme,’Null’local_net_adress,’Null’local_tcp_port,’Null’client_net_adress,’DATABASE YOK’ database_name,’Null’ dbStatus
,0 memory,0 memory,0 memory,0 memory,’Null’,’Null’,GETDATE(),0
,’Null’,’Null’,’Null’,’Null’,0,0,0,0,’Null’backupDeviceName,0,0.0,0,GETDATE() backupStartDate,GETDATE() backupFinishDate,’DB Yok’,0,GETDATE()
end

iyi günler dilerim :)