?
Solved

log shipping SqlLogShip.exe exception handling

Posted on 2014-01-25
9
Medium Priority
?
1,080 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
9 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 2000 total points
ID: 39810706
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
ID: 39813040
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
ID: 39813503
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39813504
The comment here shows to award 500 points to my comments but it never did...
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 39813683
please clarify what are your sql server version edition \sp
0
 

Author Comment

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

Author Comment

by:pae2
ID: 39813838
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

649 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