THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Get an overview report on your log shipping setups

[Updated Dec 23 to include a T-SQL script at the bottom of the post].


You may have an environment where log shipping is set up on various databases between various servers. Would it be handy to have an overview report highlighting which database is being log shipped where? Even if you have some kind of record to keep track of the databases that are being log shipped, wouldn’t it be nice to have an automated means to help validate that record?


By an overview report, I mean a three-column report that lists, for each log-shipped database, its source server, its name, and all the secondary servers.


Obviously, how to accomplish this depends on how your log shipping is set up. Let’s assume that you are using the native log shipping feature of SQL Server 2005/2008. In that case, one of the easiest approaches to collecting the log shipping information would be to run system stored procedures such as sp_help_log_shipping_monitor.


But which servers should you run sp_help_log_shipping_monitor? Well, for the report to be complete, you should just run it against all your SQL 2005/2008 instances. Or else, how do you know you are not missing some log shipped databases.


So before you can scan your environment for log shipped databases, you would, at the bare minimum, have to first provide a list of the SQL Server instances in your environment. Then, it’s just a matter of running sp_help_log_shipping_monitor against each SQL instance on the list, sort out, and combine the results. If a SQL instance contains a database that is being log shipped (regardless where this is the primary or a secondary), sp_help_log_shipping_monitor will return a list of the log-shipped databases along with their primary and secondary SQL instances. If a SQL instance does not have any log-shipped database, sp_help_log_shipping_monitor does not return anything.


By gleaning and collating information from the results of running sp_help_log_shipping_monitor against all the SQL Server 2005/2008 instances in your environment, you can then construct a comprehensive picture of the log shipping configurations in your environment. The attached Perl script does precisely that.


Note that you can’t just download the Perl script and run it against your environment as is. I’ve left a key subroutine—getProdSQL()—unfinished, and you need to fill it with the logic particular to your environment. This usually is just a few lines of code that retrieve from a database the list of the names for the SQL Server instance from that you want to scan for log shipping. I have no way of writing the logic for such routine for you.


Before you can run the script, you also need to change a few SMTP mail related parameters so that they are correct in your environment. The SMTP email routine is included so that the script can email the extract log shipping overview report to you. This is not essential, but convenient.


[Added the following section on Dec 23] 

If you have a table that stores the SQL2005/2008 instances in your environment, and you have a utility server that has a linked server configured for each of these SQL2005/2008 instances, you can also use the following simple T-SQL to scan for and retrieve the log shipping configurations in your environment:


if object_id('tempdb..#log_shipping_entries') is not null

   drop table #log_shipping_entries


create table #log_shipping_entries (

   primary_server             sysname,

   secondary_server           sysname,

   database_name              sysname



if object_id('tempdb..#log_shipping_monitor') is not null

   drop table #log_shipping_monitor


create table #log_shipping_monitor (

   status                     bit NULL,

   is_primary                 bit NULL,

   server                     sysname NULL,

   database_name              sysname NULL,

   time_since_last_backup     int NULL,

   last_backup_file           nvarchar(500) NULL,

   backup_threshold           int NULL,

   is_backup_alert_enabled    bit NULL,

   time_since_last_copy       int NULL,

   last_copied_file           nvarchar(500) NULL,

   time_since_last_restore    int NULL,

   last_restored_file         nvarchar(500) NULL,

   last_restored_latency      int NULL,

   restore_threshold          int NULL,

   is_restore_alert_enabled   bit NULL



declare @prod_server sysname,

        @sql nvarchar(2000)


declare prod_cr cursor

for select distinct ServerName

     <… You need to fill in the rest of the query to retrieve the

        Server names in your environment>


OPEN prod_cr


FETCH NEXT FROM prod_cr into @prod_server




select @sql = N'EXEC [' +

              @prod_server +



    truncate table #log_shipping_monitor

    insert #log_shipping_monitor

    EXECUTE (@sql)


    insert #log_shipping_entries

select distinct t1.server as primary_server,

                t2.server as secondary_server, t2.database_name

      from (select distinct server, database_name

              from #log_shipping_monitor

             where is_primary = 1) as t1,

           #log_shipping_monitor t2

     where t1.database_name = t2.database_name

       and t2.is_primary = 0


     FETCH NEXT FROM prod_cr into @prod_server


close prod_cr

deallocate prod_cr


select * from #log_shipping_entries


Published Monday, December 21, 2009 10:45 PM by Linchi Shea




Uri Dimant said:

Intresting Linchi, could it be done via PowerShell?

December 21, 2009 10:46 PM

Linchi Shea said:

I'm sure it could. I used Perl because I could write it dirty, quick, and effective.

December 21, 2009 11:55 PM

Linchi Shea said:

I added a simple T-SQL script (assuming you can query for the server names and have linked servers configured).

December 23, 2009 10:09 AM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement