Useful Log Shipping Script

Like all DBA’s, I have a collection of random scripts put together over time that reside in a folder and are then frequently forgotten about. Today I found a file called “useful log shipping script”, and being the generous guy I am, I’m putting it up here.

SELECT ls.primary_server AS [primaryServer]
,ls.primary_database AS [primaryDB]
,lsd.secondary_database AS [secondaryDB]
,lsd.restore_delay AS [restoreDelay]
,CONVERT(VARCHAR, DATEDIFF(SECOND, lms.last_restored_date, GETDATE()) / (60 * 60 * 24)) + '_' + CONVERT(VARCHAR, DATEADD(s, DATEDIFF(SECOND, lms.last_restored_date, GETDATE()), CONVERT(DATETIME2, '0001-01-01')), 108) AS [timeSinceLastRestore dd_hh:mm:ss]
,CONVERT (VARCHAR (20), lms.last_copied_date, 120) AS [lastCopiedDate]
,CONVERT (VARCHAR (20), lms.last_restored_date, 120) AS [lastRestoredDate]
,lms.last_copied_file AS [lastCopiedFile]
,lms.last_restored_file AS [lastRestoredFile]
,lsd.disconnect_users AS [disconnectUsers]
,ls.backup_source_directory AS [backupSourceDirectory]
,ls.backup_destination_directory AS [backupDestinationDirectory]
,ls.monitor_server AS [monitorServer]
FROM msdb.dbo.log_shipping_secondary ls
INNER JOIN msdb.dbo.log_shipping_secondary_databases lsd ON lsd.secondary_id = ls.secondary_id
INNER JOIN msdb.dbo.log_shipping_monitor_secondary lms ON lms.secondary_id = lsd.secondary_id;

Happy Scripting!

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s