February 15, 2007 at 6:55 am
Hi,
as part of a log-shipping DR solution I need an automated way of also transferring SQL agent jobs and DTS packages to the failover server. I need this so any new/amended jobs/Packages are reflected on the failover.
Unfortunately I have more than one application involved and it is possible I might have to failover just one app, so a straight restore of the msdb database might not be an option.
Anyone know if this is possible or have some suggestions for me
thanks
george
---------------------------------------------------------------------
February 16, 2007 at 8:31 am
Go to Forums Home on this site and search for "backup dts packages". Also, here's a stored procedure that I use for packages and an Activex script for jobs. The Activex script is run in a DTS package, which requires the path for the resulting file in a global variable. I've just spent several weeks preparing our DRP and this is part of it.
CREATE PROC dbo.usp_backupDTS
/***********************************************************************************
usp_backupDTS
Creates a set of SQL statements, each of which will backup one package to a
structured storage file (.DTS) in a special backup directory for DRP purposes..
-----------------------------------------------------------------------------------
CHANGE HISTORY
-----------------------------------------------------------------------------------
DATE WHO COMMENT
---------- ----------- ------------------------------------------------------------
11/17/2006 Carl Start.
***********************************************************************************/
AS
SET NOCOUNT ON
DECLARE @TARGETDIR varchar(1000), @SQL varchar(200)
CREATE TABLE #SQL (SQLStatement varchar(200))
SET @TARGETDIR = '\\PLATEDIPROD01\FTWVDSGSQL03\BackupDTS\'
INSERT INTO #SQL
SELECT distinct
'exec master.dbo.xp_cmdshell ' + '''DTSRUN.EXE /S '
+ CONVERT(varchar(200), SERVERPROPERTY('servername'))
+ ' /E '
+ ' /N '
+ '"' + name + '"'
+ ' /F '
+ '"' + @TARGETDIR + name + '.dts"'
+ ' /!X' + ''''
FROM msdb.dbo.sysdtspackages P
-----------------------------------------------------------------------------------
-- Initialize
-----------------------------------------------------------------------------------
DECLARE Command CURSOR FOR
SELECT SQLStatement
FROM #SQL
OPEN Command
FETCH NEXT FROM Command INTO @SQL
-----------------------------------------------------------------------------------
-- Cursor Loop Start
-----------------------------------------------------------------------------------
WHILE @@FETCH_STATUS = 0
BEGIN
--print @SQL
execute (@SQL)
FETCH NEXT FROM Command INTO @SQL
END
-----------------------------------------------------------------------------------
-- Cursor Loop End
-----------------------------------------------------------------------------------
close Command
deallocate Command
-----------------------------------------------------------------------------------
-- Finalize
-----------------------------------------------------------------------------------
drop table #SQL
grant all on dbo.usp_backupDTS to public
GO
'**********************************************************************
' Visual Basic ActiveX Script
' BackupServerJobs
'************************************************************************
Function Main()
Dim conServer, oJB, strJobNames, strJobScript, fso, iFile, strFilenameJobNames, strJobFile, i
strServer = DTSGlobalVariables("Server")
strJobFile = DTSGlobalVariables("JobPath")
Set fso = CreateObject("Scripting.FileSystemObject")
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect strServer
strJobScript = ""
For Each oJB In conServer.JobServer.Jobs
oJB.Name = Replace(oJB.Name,"'","")
oJB.Name = Replace(oJB.Name,"\","-")
oJB.Name = Replace(oJB.Name,"/","-")
strJobScript = strJobScript & "--------------------------------------------------" & vbCrLf
strJobScript = strJobScript & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJobScript = strJobScript & "--------------------------------------------------" & vbCrLf
strJobScript = strJobScript & oJB.Script() & vbCrLf & vbCrLf
Next
strJobScript = strJobScript & "use msdb" & vbCrLf
strJobScript = strJobScript & "DECLARE @srv sysname" & vbCrLf
strJobScript = strJobScript & "SET @srv = CAST(SERVERPROPERTY('ServerName') AS sysname)" & vbCrLf
strJobScript = strJobScript & "UPDATE sysjobs SET originating_server = @srv" & vbCrLf
Set iFile = fso.CreateTextFile(strJobFile, True)
iFile.Write (strJobScript)
iFile.Close
Set conServer = Nothing
Set fso = Nothing
Set iFile = Nothing
Main = DTSTaskExecResult_Success
End Function
February 27, 2007 at 4:23 am
Carlos, many thanks most useful.
Another approach I was comsidering was stored procs that alert whenever a sqlagent job or DTS package is created/amended so I can ensure they are updated on the standby server. If these would be useful to you let me know by reply and I will enclose them and post them on the site
george
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply