none
TSQL to check if a DB is the primary in log shipping RRS feed

  • Question

  • 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

    Friday, August 28, 2015 9:44 PM

Answers

  • 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'

    Friday, August 28, 2015 10:36 PM

All replies

  • 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

    Friday, August 28, 2015 9:59 PM
  • 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'

    Friday, August 28, 2015 10:36 PM