February 28, 2012 at 2:35 am
Hi Team,
In a instance we have around 400 database in this one some of the database are configured logshipping
How can we find out database was configured in Logshipping or not ?
can you people give some script for this.
Tx
February 28, 2012 at 3:19 am
this url will help http://msdn.microsoft.com/en-us/library/ms175106.aspx
the table your looking for is log_shipping_monitor_primary which details all the source db's which are involved in logshipping
February 28, 2012 at 4:22 am
Hi Anthony
Thanks for your reply
How can we find databases which are not configured in logshipping on a server
Tx
February 28, 2012 at 4:50 am
as the primary table contains DB's which are logshipping configured you wuold just tie it back to sys databases where its not in the primary table
February 28, 2012 at 6:59 am
Hi Team,
as per your suggestion i have created below script but it is giving error
select
LP.Primary_Database as LogshippingDB
,sd.Name AS NotLogshippingDB
From msdb.log_shipping_monitor_primary LP
Inner Join Master.dbo.sysDatabases SD on dbid.SD = dbid.sd
inner join msdb.dbo.log_shipping_monitor_primary LP on PRIMARY_ID.LP = primary_id.lp
Error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'msdb.log_shipping_monitor_primary'.
any suggestion please
February 28, 2012 at 7:03 am
your syntax is wrong for the whole query
you need the 3 part identifyer
msdb.dbo.log_shipping_monitor_primary
when you correct it you will get
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "dbid.SD" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "dbid.sd" could not be bound.
Msg 1011, Level 16, State 1, Line 1
The correlation name 'LP' is specified multiple times in a FROM clause.
February 28, 2012 at 7:13 am
you will want something like this
select
LP.Primary_Database as LogshippingDB,
'' AS NotLogshippingDB
from
msdb..log_shipping_monitor_primary lp
union
select
'',
sd.Name
FROM
sys.databases SD
LEFT OUTER JOIN
msdb.dbo.log_shipping_monitor_primary lp
ON
sd.name = lp.primary_database
WHERE
lp.primary_database is null
February 28, 2012 at 8:00 am
Hi Team,
I got this answer Thanks your for your suggestion.,
select lp.primary_server, sd.name, case when lp.primary_database IS null then 'Yes' else 'No' end LSConfigured
From
Master.dbo.sysDatabases SD
left join msdb.dbo.log_shipping_monitor_primary LP on lp.primary_database=SD.name
February 28, 2012 at 8:04 am
yeah that will work, but in the case you will want it to be IS NOT NULL as I have just run this on my test machine with NO logshipping and its saying they are all logshipped
February 28, 2012 at 8:11 am
Hi
at that time we will do in this way any thing wrong please correct me
select lp.primary_server, sd.name, case when lp.primary_database IS NOT NULL then 'Yes' else 'NO' end LSConfigyred
From
Master.dbo.sysDatabases SD
left join msdb.dbo.log_shipping_monitor_primary LP on lp.primary_database=SD.name
February 28, 2012 at 8:27 am
if it gives you the information you need then its fine
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply