Hi,

 I have a server that has several DBs .. some are log shipping(ls_) and others are not. The current TRAN backup script just does a:

select name from sys.databases

where name !=  'ls_xxxx'

and name != 'ls_yyyyy'

Is there a way to check a status byte or something so I can remove the hard coded names?

select name from sys.sysdatabase

where status   =  'I log ship'  -- ;)

I checked several places, but found nothing. Finding other status like RECOVERING et al ... is easy

thanks

eric

Yes, you can use the sp_help_log_shipping_monitor system stored procedure. There is an is_primary column. This will return rows for databases that are involved in log shipping.

https://msdn.microsoft.com/en-us/library/ms187820.aspx?f=255&MSPPError=-2147217396

• Proposed as answer by Shanky_621MVP 2 hours 34 minutes ago

Hi,

 Thank you. I looked into proc and found a snippet for my own use.

-- TEST 

declare @DBCheck varchar(100) = 'LS_DB'

-- declare @DBCheck varchar(100) = 'NOT_LS_DB'

 

 

IF NOT EXISTS   (

 select  primary_database

       from msdb.dbo.log_shipping_monitor_primary with (nolock)

        where primary_database = @DBCheck )

BEGIN

PRINT  'NOT a primary LS DB DO A TRAN LOG BACKUP'

ELSE

PRINT 'This is a primary LS DB SKIP IT'

Yes, you can use the sp_help_log_shipping_monitor system stored procedure. There is an is_primary column. This will return rows for databases that are involved in log shipping.

https://msdn.microsoft.com/en-us/library/ms187820.aspx?f=255&MSPPError=-2147217396

• Proposed as answer by Shanky_621MVP Saturday, August 29, 2015 4:33 AM