restore MSDB or transfer jobs?

I restored MSDB onto a new box and my jobs won't run now:
Unable to connect to SQL Server '(local)'. The step failed.
Am I better off to use the Transfer Jobs Task in DTS or is there a simple
answer to this? If so, what about moving the DTS Packages?
SQL2K SP3
TIA, ChrisR
I would scrip them out an apply on new server. But you might be able
to fix them on the new server
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('ServerName') as
nvarchar(50))
update msdb..sysjobs
set originating_server = @.NewServerName
GO
checkpoint
GO
|||The query converts the @.NewServerName to nothing:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'originating_server', table
'msdb.dbo.sysjobs'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('MyBox') as nvarchar(50))
print @.NewServerName
However, I did do an old fashioned:
Update SysJobs
set originating_server = 'MyBox'
but still I have the same problem.
If I did the script method, what would I do with my DTS Packages? Just save
to the new box? Would I be missing anything then from NOT restoring MSDB?
What do most of you do in this scenario?
"bert" <bertcord@.gmail.com> wrote in message
news:1106868585.875164.102430@.z14g2000cwz.googlegr oups.com...
> I would scrip them out an apply on new server. But you might be able
> to fix them on the new server
> declare @.NewServerName varchar(50)
> select @.NewServerName = cast(serverproperty ('ServerName') as
> nvarchar(50))
> update msdb..sysjobs
> set originating_server = @.NewServerName
> GO
> checkpoint
> GO
>
|||select serverproperty ('ServerName') returns null?
Sorry cant help you with the DTS jobs... I stay away from DTS.
did you stop and restart SQL
Bert
|||Have a look here: http://www.sqldts.com/default.aspx?204
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Not an option. Thanks Bert.
"bert" <bertcord@.gmail.com> wrote in message
news:1106878056.175663.124700@.z14g2000cwz.googlegr oups.com...
> select serverproperty ('ServerName') returns null?
> Sorry cant help you with the DTS jobs... I stay away from DTS.
> did you stop and restart SQL
> Bert
>
|||Thanks Andrew.
The "Save as" option suits me just fine. But assuming I do that in place of
Restoring MSDB, what about the jobs? Both the jobs to run these DTS Packages
and those that don't. I know the other options are to script them or to use
DTS to transfer them. Is anyone aware of the pros/ cons for either one? If I
do either of these methods, will I be missing something vital from NOT
Restoring MSDB? I don't think so based on past experience, but its been a
while.
TIA, ChrisR
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||I prefer to script the jobs myself but I don't know of any thing that says
that is better or worse than moving MSDB per say. Even the KB that talks
about moving DB's suggests moving jobs via scripting.
http://www.support.microsoft.com/?id=314546
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew.
> The "Save as" option suits me just fine. But assuming I do that in place
> of
> Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> Packages
> and those that don't. I know the other options are to script them or to
> use
> DTS to transfer them. Is anyone aware of the pros/ cons for either one? If
> I
> do either of these methods, will I be missing something vital from NOT
> Restoring MSDB? I don't think so based on past experience, but its been a
> while.
> TIA, ChrisR
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks again Andrew. I think Im going to change my strategy on this whole
thing. It seems like to "Save as" for the DTS Packages and Script the Jobs/
Operators/ and Alerts I could get everything that I would from Restoring
MSDB minus a few headaches.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#AVGIwUBFHA.3924@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I prefer to script the jobs myself but I don't know of any thing that says
> that is better or worse than moving MSDB per say. Even the KB that talks
> about moving DB's suggests moving jobs via scripting.
> http://www.support.microsoft.com/?id=314546
>
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
If[vbcol=seagreen]
a[vbcol=seagreen]
simple
>