August 30, 2018 at 6:04 am
Good Morning Experts,
I have a SQL server A and it has 180 Agent jobs. I want to copy all these jobs to another SQL Server B. Can i restore msdb database from A to B? After that what needs to be done. Could you please share step-by-step details
August 30, 2018 at 6:10 am
msdb can only be copied over if servers are on the same patch level.
As an alternative you can go to the Jobs, in Object Explorer Details you can then mark all jobs you want to copy, right click, Script Job as, Create To ...
August 30, 2018 at 6:24 am
DinoRS - Thursday, August 30, 2018 6:10 AMmsdb can only be copied over if servers are on the same patch level.As an alternative you can go to the Jobs, in Object Explorer Details you can then mark all jobs you want to copy, right click, Script Job as, Create To ...
I tried that but SSMS is getting hung and not responding
August 30, 2018 at 6:44 am
Then try to split into more files, 20 Jobs get scripted out like instantly here, maybe try less than 180 Jobs at a time, say 30 or 60 so you would get 3 - 6 files.
In theory you can query out all the Jobs from msdb.dbo sysjobs* Tables. Another approach is described here: http://annevamsikrishna.blogspot.com/2012/02/recovering-sql-agent-jobs.html#!/2012/02/recovering-sql-agent-jobs.html essentially you would restore your msdb as msdb_old or something like that on the new server and extract the info. In the comments you'll see how to copy over the schedules aswell. Generally this sounds much less intrusive than using a "foreign" msdb on a new instance.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy