SQLTeam.com | Weblogs | Forums

Sql server jobs restore


#1

Hi,
We are using sql 2005 and there are about 50-60 sql jobs on our sql server.
Read that it is stored in msdb database. Is it possible
to backup and restore jobs to another server (2012 version) as we do to restore the
databases. Will the timings be automatically set or do we need to
configure again for the 50-60 jobs?

Thanks


#2

Try scripting them:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fdfc00d7-ea7b-4484-84d3-fa25f7b82b65/how-to-script-all-sql-jobs


#3

it would be better off to script them out and re-create the job in different version of SQL, it can ensure everything is in place.


#4

I have a vague recollection that there is an ID or a GUID or something that needs removing from the script (to allow the new server to assign a fresh ID). Can't remember what it was though ... hang on let me check a script from the last server migration we did ...

Hmmm ... my notes in the script say:

Change script so all jobs are DISABLED (and then ENABLE them once everything else is ready, so the jobs don't start prematurely!)

Make a report of which ones were originally Enabled / Disabled - so don't accident enable a disabled one!

-- Check all @notify_email_operator_name exist!
-- List: EXEC msdb.dbo.sp_help_operator

However, I can't see any obvious ID that I commented out ...