If log shipping is set up on your databases, it would be handy to have an overview report highlighting which database is being log shipped and checked whether they configured as a primary or a secondary server.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
USE [master]; GO DECLARE @log_shipping TABLE ( status bit, is_primary bit, server sysname, database_name sysname, time_since_last_backup int, last_backup_file nvarchar(500), backup_threshold int, is_backup_alert_enabled bit, time_since_last_copy int, last_copied_file nvarchar(500), time_since_last_restore int, last_restored_file nvarchar(500), last_restored_latency int, restore_threshold int, is_restore_alert_enabled bit ) INSERT INTO @log_shipping EXEC sp_help_log_shipping_monitor SELECT @@SERVERNAME as [Server Name], d . name As [Databse Name], CASE WHEN l.is_primary=0 OR l.is_primary=1 THEN 'Configured' ELSE 'Not Configured' END AS [Log Shipping Configured?], CASE WHEN l.is_primary=0 THEN 'Secondary Database' WHEN l.is_primary=1 THEN 'Primary Database' ELSE NULL END AS [Log Shipping Type], time_since_last_backup AS [Time_since_last_backup (Min)], time_since_last_restore AS [Time_since_last_restore (Min)] FROM sys.databases d LEFT JOIN @log_shipping l ON d.name=l.database_name AND l.server=@@SERVERNAME WHERE d.database_id > 4 ORDER BY d.NAME |
Leave a Comment