How to Scan and Get Log Shipped Databases in SQL Server

“Log Shipping Alert” is a job category that is created on the SQL Server instance whenever the log shipping is enabled on a database. Though this might seem to be an easy way to identify if log shipping is running on a particular SQL server, through TSQL we can scan the entire SQL Server environment to identify databases that are participating in Log Shipping.

2 tables

SQL Server Log ShippingLog shipped databases can be easily identified using two tables in msdb database. The first table is the log_shipping_primary_databases and the second table is log_shipping_primary_secondaries.  The table ‘log_shipping_primary_databases’ not only provides the names of log shipped databases but also gives information about the backup directory, retention period for backup files, the name of the monitoring server, time, recent log backup’s date, time and path. The table log_shipping_primary_secondaries gives information about the secondary server and secondary databases.

Scan it

TSQLIn this script, we would be connecting to each linked server from a Central server and then we would be extracting information about log shipped databases. Please do note, this script will not create any linked server. Before executing this script, please make sure that you have created linked servers. As a first step, we would be loading name of all linked servers in a table. Through the script, we would be creating two more tables. One is to store the command that would be executed on each linked server and another table is to log data that was collected from each linked server.  The result would show Primary Server name, Primary database name, Secondary server name, and Secondary server database

DECLARE @Tserver TABLE 
     ( 
         cserver VARCHAR(200) 
     )

INSERT INTO @Tserver 
VALUES      ('Server1')

DECLARE @Tcmd TABLE 
     ( 
         ccmd VARCHAR(2000) 
     ) 
DECLARE @Tlog TABLE 
     ( 
         p_server VARCHAR(200), 
         p_database VARCHAR(200), 
         s_server VARCHAR(200),
         s_database varchar(200) 
    )

INSERT INTO @Tcmd 
SELECT 'exec (''Select @@servername , a.primary_database,b.secondary_server,b.secondary_database from msdb.dbo.log_shipping_primary_databases a join msdb.dbo.log_shipping_primary_secondaries b on a.primary_id = b.primary_id'') at [' 
       + cserver + ']' 
FROM @Tserver

DECLARE c1 CURSOR FOR 
     SELECT * 
     FROM @Tcmd 
DECLARE @cmd VARCHAR(2000)

OPEN c1

FETCH next FROM c1 INTO @cmd

WHILE @@FETCH_STATUS = 0 
     BEGIN 
         PRINT @cmd

         INSERT INTO @Tlog 
                     (p_server,p_database,s_server,s_database 
                     ) 
         EXEC(@cmd)

         FETCH next FROM c1 INTO @cmd 
     END

CLOSE c1

DEALLOCATE c1

SELECT * 
FROM @Tlog

Log shipping: Old and still used widely

To attain high availability, Log shipping is a very old solution available in Microsoft SQL Server. However, it is still used by many database administrators because of its simplicity in setting it up on SQL environment. Since backup and restore are the backbone of log shipping, many database administrators find it easy to trouble shoot and fix it. Once it is set up, we will rarely see log shipping job fail. One common error message that we would probably see is: “The log in this backup set begins at LSN %.*ls, which is too recent to apply to the database. An earlier log backup that includes LSN %.*ls can be restored.”

When a SQL corruption occurs, you can easily change roles between primary and secondary log shipping servers.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook repair and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.