Solved

log shipping SqlLogShip.exe exception handling

Posted on 2014-01-25
9
967 Views
Last Modified: 2014-02-01
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.

Thanks,

pae2

SELECT primary_id
INTO #LSTemp
FROM msdb.dbo.log_shipping_primary_databases;

-- enable the feature
EXEC sp_configure 'xp_cmdshell', 1
GO
-- update the currently configured value for this feature
RECONFIGURE
GO

-- loop through table if rows exist
DECLARE @ID uniqueidentifier
DECLARE @CMD varchar(300)

WHILE EXISTS(SELECT 1 primary_id FROM #LSTemp)

BEGIN -- begin while

BEGIN TRY
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;
END TRY

BEGIN CATCH
SELECT
CAST(@ID AS varchar(50)) AS ':log backup failed', ERROR_MESSAGE() AS ErrorMessage, GETDATE() AS ErrorDateTime;
-- RETURN(1)
END CATCH

END -- end while

RETURN

Open in new window

0
Comment
Question by:pae2
  • 4
  • 2
9 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 500 total points
Comment Utility
this might work for you,

The below code is not tested, this only work assuming SQLLogship.exe returns an error to XP_CMDSHELL upon time out or else it will not.


SELECT primary_id
INTO #LSTemp
FROM msdb.dbo.log_shipping_primary_databases;

-- enable the feature
EXEC sp_configure 'xp_cmdshell', 1
GO
-- update the currently configured value for this feature
RECONFIGURE
GO

-- loop through table if rows exist
DECLARE @ID uniqueidentifier
DECLARE @CMD varchar(300)
DECLARE @ret_code INT

WHILE EXISTS(SELECT 1 primary_id FROM #LSTemp)

BEGIN -- begin while

BEGIN TRY
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 @ret_code = xp_cmdshell @CMD, no_output
if (@ret_code = 1)
   RAISERROR('Errored with logshipping ' + CAST(@ID AS varchar(50)),16,1)

DELETE FROM #LSTemp WHERE @ID=primary_id;
END TRY

BEGIN CATCH
SELECT
CAST(@ID AS varchar(50)) AS ':log backup failed', ERROR_MESSAGE() AS ErrorMessage, GETDATE() AS ErrorDateTime;
-- RETURN(1)
END CATCH

END -- end while

RETURN

Open in new window

0
 

Accepted Solution

by:
pae2 earned 0 total points
Comment Utility
Thanks for the help. I'm not sure if what you suggested works. But it's similar to what I did. I dropped the try/catch code. Then did the following which does work!

DECLARE @RETURNCODE INT
EXEC @RETURNCODE=xp_cmdshell @CMD, no_output

-- will return 1 if there is a failure and 0 if it executed successfully
IF @RETURNCODE<>0
BEGIN
-- SOME CODE
END

Open in new window

0
 

Author Comment

by:pae2
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for pae2's comment #a39813040
Assisted answer: 500 points for Neo_jarvis's comment #a39810706

for the following reason:

My code definitely works. I'm not sure if Surendra Ganti's will work, but it looks good to me.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 16

Expert Comment

by:Surendra Nath
Comment Utility
The comment here shows to award 500 points to my comments but it never did...
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
please clarify what are your sql server version edition \sp
0
 

Author Comment

by:pae2
Comment Utility
EugeneZ this has been resolved already. So no need to answer. But we're using SQL Server 2008R2.
0
 

Author Comment

by:pae2
Comment Utility
Surendra Ganti, I tried to award the points. I'm not sure what's wrong. The question isn't closed yet. Maybe EE can fix it. I'll respond to whatever questions they have.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now