July 15, 2013 at 6:32 am
Hi all,
I am really hard to finding how many databases are under logshipping in a given server. also that script need to get databases (means that script support any version) from 2000 version to 2012. can any one please help me out from this issue. that would be great. thanks
regards
mac
July 15, 2013 at 8:29 am
Running the below query will give a list of all databases enabled with Log shipping their status
use master
exec sp_help_log_shipping_monitor
Regards,
July 15, 2013 at 8:59 am
mac_live (7/15/2013)
Hi all,I am really hard to finding how many databases are under logshipping in a given server. also that script need to get databases (means that script support any version) from 2000 version to 2012. can any one please help me out from this issue. that would be great. thanks
regards
mac
These should help
--===============================================================--
--============== Against the Primary run this =================--
--===============================================================--
SELECTlspd.primary_database AS PrimaryLogShipDB
, lspd.backup_directoryAS LocalBakDir
, lspd.backup_share AS UNCSharePath
, sj.name AS AgentBakJobName
, lspd.backup_retention_period / 60 AS Pri_RetentionInHours
, lspd.monitor_server AS MonitorSrvr
, lspd.last_backup_date AS LastBakDate
FROM msdb.dbo.log_shipping_primary_databases lspd
INNER JOIN msdb.dbo.sysjobs sj
ON lspd.backup_job_id = sj.job_id
--===============================================================--
--===============================================================--
--============ Against the Secondary run this ================--
--===============================================================--
SELECTlssd.secondary_database AS SecondaryLogShipDB
, lssd.restore_delay / 60 AS RestoreDelayInHours
, CASE lssd.restore_all
WHEN 0 THEN 'Restore single file'
WHEN 1 THEN 'Restore all files'
END AS RestoreAllFiles
, CASE lssd.restore_mode
WHEN 0 THEN 'NORECOVERY'
WHEN 1 THEN 'STANDBY'
END AS RestoreMode
, CASE lssd.disconnect_users
WHEN 0 THEN 'NoDisconnect'
WHEN 1 THEN 'Disconnect Users'
END AS DisconnectUsrs
, lsd.backup_destination_directory AS BackupDestDir
, lsd.file_retention_period / 60 AS Sec_RetentionInHours
, sj.name AS AgentCopyJobName
, sj2.name AS AgentResJobName
FROM dbo.log_shipping_secondary_databases lssd
INNER JOIN msdb.dbo.log_shipping_secondary lsd
ON lssd.secondary_id = lsd.secondary_id
INNER JOIN msdb.dbo.sysjobs sj
ON lsd.copy_job_id = sj.job_id
INNER JOIN msdb.dbo.sysjobs sj2
ON lsd.restore_job_id = sj2.job_id
--===============================================================--
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 15, 2013 at 12:00 pm
Thanks mate.
July 16, 2013 at 3:27 am
you're welcome
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply