How to check Database mirroring is enabled on MS SQL Server or Log shipping is enabled

• georgian2133

  SSC Veteran

  Points: 298

  More actions

  May 12, 2017 at 8:19 am

  #323540

  How to check Database mirroring is enabled  or Log shipping is enabled on MS SQL Server server ?

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  May 12, 2017 at 8:55 am

  #1942353

  georgian2133 - Friday, May 12, 2017 8:19 AM

  How to check Database mirroring is enabled  or Log shipping is enabled on MS SQL Server server ?

  One option is that you could query the tables that would have information for any databases involved in mirroring or log shipping.
  SELECT * FROM sys.database_mirroring
SELECT * FROM log_shipping_primary_databases

  database_mirroring will have a row for every database but the values of the columns with the name starting with mirroring_xxx would be null. So I guess you could get the database names for those using something like:
  SELECT d.name as MirroredDB
FROM sys.database_mirroring m
INNER JOIN sys.databases d on
m.database_id = d.database_id
WHERE m.mirroring_guid is not null

  SELECT primary_database as LogShippedDB
FROM msdb.dbo.log_shipping_primary_databases
WHERE primary_database is not null
  Sue

• georgian2133

  SSC Veteran

  Points: 298

  More actions

  May 12, 2017 at 9:15 am

  #1942358

  Thank you Sue for quick reply.

  I have checked on our production server with the commands you shared and its give me the details as i need.
  In current environment Mirroring is enabled.