Log shipping creates three jobs per-db. We have lots of dbs, so that's not going to work. I wrote a script that copies logs from one server to the next successfully. I'm having some problems with the exception handling. My script grabs all of the log shipping db ids on the primary puts them in a temp table then grabs one id at a time to perform a log backup. All of that works well. I'm using the querytimeout parameter so that sqllogship.exe skips logs backups that take longer than 2-minutes. That is represented by the 120. That also works. What I need help with is a way to send an error message to the CATCH letting me know that a log backup was skipped for a particular db. Does anyone have any ideas? I am open to restructuring the error-handling, if needed. I want to avoid using the alert jobs because I'm trying to make this happen with a single job.
-- enable the feature
EXEC sp_configure 'xp_cmdshell', 1
-- update the currently configured value for this feature
-- loop through table if rows exist
DECLARE @ID uniqueidentifier
DECLARE @CMD varchar(300)
WHILE EXISTS(SELECT 1 primary_id FROM #LSTemp)
BEGIN -- begin while
SELECT TOP 1 @ID=primary_id FROM #LSTemp;
SET @CMD = '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SqlLogShip.exe" -backup ' + CAST(@ID AS varchar(50)) + ' -server SQLCONDEV02 -querytimeout 120 -verboselevel 2'
EXEC xp_cmdshell @CMD, no_output
DELETE FROM #LSTemp WHERE @ID=primary_id;
CAST(@ID AS varchar(50)) AS ':log backup failed', ERROR_MESSAGE() AS ErrorMessage, GETDATE() AS ErrorDateTime;
END -- end while