Sql-Server
DBA 新工作的第一天 - 檢查備份和安全性 - 如何？還應該檢查什麼？
通常，當我在新環境中開始時，我傾向於檢查備份在哪裡，最後一次完整的時間，最後一次恢復的時間，我也會檢查安全性。
我這樣做的方式是通過 T-SQL。
檢查備份
;with Radhe as ( SELECT @@Servername as [Server_Name], B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup, ISNULL(Convert(char(11), MAX(backup_finish_date), 113)+ ' ' + CONVERT(VARCHAR(8),MAX(backup_finish_date),108), 'NEVER') as LastBackupDate ,BackupSize_GB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2)) ,BackupSize_MB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2)) ,media_set_id = MAX(A.media_set_id) ,[AVG Backup Duration]= AVG(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int)) ,[Longest Backup Duration]= MAX(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int)) ,A.type FROM sys.databases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name AND A.is_copy_only = 0 AND (A.type = 'D') --'D' full, 'L' log GROUP BY B.Name, A.type ) SELECT r.[Server_Name] ,r.Database_Name ,[Backup Type] = r.type ,r.DaysSinceLastBackup ,r.LastBackupDate ,r.BackupSize_GB ,r.BackupSize_MB ,F.physical_device_name ,r.[AVG Backup Duration] ,r.[Longest Backup Duration] FROM Radhe r LEFT OUTER JOIN msdb.dbo.backupmediafamily F ON R.media_set_id = F.media_set_id ORDER BY r.Server_Name, r.Database_Name檢查安全性：
檢查恢復：
DECLARE @dbname sysname, @days int SET @dbname = NULL --substitute for whatever database name you want SET @days = -30 --previous number of days, script will default to 30 SELECT rsh.destination_database_name AS [Database], rsh.user_name AS [Restored By], CASE WHEN rsh.restore_type = 'D' THEN 'Database' WHEN rsh.restore_type = 'F' THEN 'File' WHEN rsh.restore_type = 'G' THEN 'Filegroup' WHEN rsh.restore_type = 'I' THEN 'Differential' WHEN rsh.restore_type = 'L' THEN 'Log' WHEN rsh.restore_type = 'V' THEN 'Verifyonly' WHEN rsh.restore_type = 'R' THEN 'Revert' ELSE rsh.restore_type END AS [Restore Type], rsh.restore_date AS [Restore Started], bmf.physical_device_name AS [Restored From], rf.destination_phys_name AS [Restored To] FROM msdb.dbo.restorehistory rsh INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days --AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all ORDER BY rsh.restore_history_id DESC GO複製：
--first thing - go to the publisher DB and find out the distributor server and DB sp_helpdistributor -- go to the distributor server and DB found above and run the following: sp_replmonitorhelppublication null鏡像：
--============================================================================== -- query that shows the current state of each database in the mirroring --============================================================================== SELECT db_name(sd.[database_id]) AS [Database Name] ,sd.mirroring_state AS [Mirror State] ,sd.mirroring_state_desc AS [Mirror State] ,sd.mirroring_partner_name AS [Partner Name] ,sd.mirroring_role_desc AS [Mirror Role] ,sd.mirroring_safety_level_desc AS [Safety Level] ,sd.mirroring_witness_name AS [Witness] ,sd.mirroring_connection_timeout AS [Timeout(sec)] FROM sys.database_mirroring AS sd WHERE mirroring_guid IS NOT null ORDER BY [Database Name];始終開啟
-- Always On Status Report -- -- This script will show the status of the Alway On replication status SELECT DISTINCT primary_replica as 'Primary Server', [endpoint_url] as 'End Point URL', primary_recovery_health_desc as 'Primary Server Health Status', secondary_recovery_health_desc as 'Secondary Server Health Status', operational_state_desc as 'Operational State', connected_state_desc as 'Connection State', recovery_health_desc as 'Recovery Health', synchronization_state_desc as 'Synchronization State', database_state_desc as 'Database State', JOIN_state_desc as 'Join State', suspend_reason_desc as 'Suspended Reason', availability_mode_desc as 'Availability Mode', failover_mode_desc as 'Failover Mode', primary_role_allow_connections_desc as 'Primary Connections Allowed', secondary_role_allow_connections_desc as 'Secondary Connections Allowed', create_date as 'Date Created', modify_date as 'Date Modified', [backup_priority] as 'Backup Priority', role_desc as 'Role Type', last_connect_error_description as 'Last Connection Error', last_connect_error_timestamp as 'Last Connection Error Time', last_sent_time as 'Last Data Send Time', last_received_time as 'Last Data Recieved TIme', last_hardened_time as 'Last Hardened Time', last_redone_time as 'Last Redone Time', log_send_queue_size as 'Log Send Queue Size', log_send_rate as 'Log Send Rate', redo_queue_size as 'Redo Queue Size', redo_rate as 'Rate of Redo', filestream_send_rate as 'Filestream Send Rate', last_commit_time as ' Last Commit Time', low_water_mark_for_ghosts as 'Low Water Mark for Ghosts' FROM sys.dm_hadr_availability_group_states JOIN sys.availability_replicas ON sys.dm_hadr_availability_group_states.group_id = sys.availability_replicas.group_id JOIN sys.dm_hadr_availability_replica_cluster_states ON sys.dm_hadr_availability_group_states.group_id = sys.dm_hadr_availability_replica_cluster_states.group_id JOIN sys.dm_hadr_availability_replica_states ON sys.dm_hadr_availability_group_states.group_id = sys.dm_hadr_availability_replica_states.group_id JOIN sys.dm_hadr_database_replica_states ON sys.dm_hadr_availability_group_states.group_id = sys.dm_hadr_database_replica_states.group_id WHERE operational_state_desc IS NOT NULL AND database_state_desc IS NOT NULL ORDER BY [endpoint_url] DESC -- Testing section /* -- Suspend replication from primary ALTER DATABASE [AdventureWorksLT2008] SET HADR SUSPEND -- Resume replication from secondary ALTER DATABASE [AdventureWorksLT2008] SET HADR RESUME -- Force a manual failover of replication with data loss. MUST EXECUTE ON SECONDARY SQL SERVER ALTER AVAILABILITY GROUP [AG-AdventureWorksLT2008] FORCE_FAILOVER_ALLOW_DATA_LOSS; -- Force a manual failover of replication with NO data loss MUST EXECUTE ON SECONDARY SQL SERVER --- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE. --Connect VDV1OPS03 ALTER AVAILABILITY GROUP [AG-AdventureWorksLT2008] FAILOVER; GO --- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE. --:Connect OPSDBSRV ALTER AVAILABILITY GROUP [AG-AdventureWorksLT2008] FAILOVER; GO */日誌運輸：
題：
除了上述之外，在開始新環境時我還應該優先檢查其他事項嗎？
正是出於這個原因，我編寫了免費（和開源）的sp_Blitz。
人們不停地遞給我 SQL Server，然後說：“你是 DBA，你來管理這件事。” 我需要一些可以快速分析以下內容的東西：
- 尚未備份或檢查損壞的數據庫
- 不受支持的 SQL Server 版本
- 危險的跟踪標誌和數據庫設置
- 錯誤的 sp_configure 選項
只需執行它，無需任何參數，您將獲得優先的健康檢查。優先級 1-50 是您想要立即開始的事情，優先級 51+ 是需要注意的事情，以備後用。
如需支持或貢獻程式碼，請前往FirstResponderKit.org的 Github 儲存庫。