help a baby DBA transfer jobs from one server to another

Question

help a baby DBA transfer jobs from one server to another

alen teplitsky

SSC-Dedicated

Points: 30014
More actions
November 16, 2006 at 1:17 pm

#115087

Started some of the prelimenary work on getting ready for a SQL 2005 upgrade by transfering jobs from production to one of the test servers.
I'm having trouble transfering jobs when the database mentioned in the job is not on the destination server. Is there a workaround?
And can someone help me building an expression? We probably have a few jobs that have the same name across servers. I want to add a _servername to the end of each job transfered to identify the server it came from.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Benedikt Ries Mr or Mrs. 500 Points: 503 More actions · Answer 1

Eeeerh, what do you mean with "building an expression"?

1.) The DB you want to pull data from is not on the destination Server? That shouldn´t be a problem, just fix the connection manager for that source.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

i was getting an error where i couldn't transfer the job from one server to another.

We have 8 main production SQL 2000 servers and a few test SQL 2005 servers. We wanted to transfer all the jobs to one of the SQL 2005 servers as a backup because we were thinking of completely reinstalling the OS next month for the upgrade.

The test SQL2005 server in question does not have all the databases of the production servers and i got an error transfering the jobs over. Don't want to script them because when first setting up the environment i had problems recreating some jobs from script.

Also wanted to transfer the jobs and add a _servername to the end of the name to identify which server they came from

Carlos Urbina SSCommitted Points: 1850 More actions · Answer 3

We are in the process of backing up server objects, databases, logins, DTS packages, SQL Jobs, etc., for Disaster Recovery. Here's the Activex script we used to backup the jobs as script. We run it in an Activex task in a DTS package and it works fine. Note that the fully-qualified UNC for the backup location is in the mentioned global variable. All jobs are scripted to a single file. I received help from Nigel Rivett at http://www.nigelrivett.net/ScriptDTSProperties.html

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

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

iFile.Write (strJobScript)

iFile.Close

Set conServer = Nothing

Set fso = Nothing

Set iFile = Nothing

Main = DTSTaskExecResult_Success

End Function