“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.
Log 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.
In 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.
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