September 13, 2009 at 5:59 am
Hi Everyone,
I've been asked to migrate a few databases. I've successfully moved across the databases to the new server but I'm just wondering how you transfer the jobs over properly.
I've right clicked on each job and ran the script>create to file, then ran the script on the new server. The job creates but it doesn't actually populate the steps or schedule within the job itself.
There was a few Blackberry databases that I also moved, I followed the correct procedure as laid out in the documentation and it created the jobs on the SQL server but it never populated the contents within the job.
I'm just wondering if there is an extra step to take or a command I can run to export the job and it's contents? Or if it's possible to backup the database where the jobs are stored and restore it to the new server?
Thanks for looking
September 13, 2009 at 6:39 am
Scripting should ideally bring job steps as well.
There is another method... Import the table contents of sysjobs, sysjobsteps and sysjobschedules tables from msdb database of the source server to the msdb database of the target server.
September 14, 2009 at 2:20 am
ps (9/13/2009)
Scripting should ideally bring job steps as well.There is another method... Import the table contents of sysjobs, sysjobsteps and sysjobschedules tables from msdb database of the source server to the msdb database of the target server.
I would try again with the scripting of the jobs, I wouldnt advise importing any information from the mdsb database.
script the jobs to new query window and check that it matches what is in the job.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 14, 2009 at 9:53 am
hmmm ... could it be that these are Maintenance Plans ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 15, 2009 at 4:27 am
SQL Server FineBuild has scripts that help you export and import jobs. Feel free to raid the code for whatever you want.
There is a problem area moving jobs that are part of a maintenance plan. The steps in these jobs refer to the maintenance plan, but the plan itself is not migrated. This means the imported job will not run correctly.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 16, 2009 at 5:31 am
Ok, I've tried scripting out the jobs once more and was unsuccessful. I've also tried using the export jobs task from the Business Intelligence Studio, it creates the jobs fine but doesn't include the contents of the jobs. These are stand alone jobs with no maintenance plans on the server.
But here's the strange thing, if I use SSMS from a different computer and connect to the new SQL server and run the script the jobs are created and the contents are populated successfully. If I run SSMS from the new server and connect to the old sql server and run the script against the old server, the job and contents are created fine.
It's only when I run the script from the new SQL Server SSMS, connected to the local engine is when it doesn't work properly. Or trying to use SSIS to transfer the jobs over.
Have I got a really wierd problem?
EDIT: I've just looked at the jobs using a different server's SSMS and the jobs are in fact populated. They just don't appear populated when using the SMSS on the new server.
So it's a bug with SSMS?
September 16, 2009 at 6:03 am
Ok, I've tried scripting out the jobs once more and was unsuccessful.
What do you mean "unsuccessful"?
Just click on "Jobs" node in the Object Explorer of SSMS, then highlight all jobs on the right pane, do the right click on highlighted jobs and script them.
September 16, 2009 at 6:03 am
Other your weird issues probably related to the discrepancies of SQL Server versions on your servers (different services packs).
September 16, 2009 at 6:14 am
what I mean by 'unsuccessful' was the script created but when I ran it from the new server it created the jobs but not the contents.
I just noticed that the new SQL server box is using a slight older version of the SSMS, even though both machines have been service packed to the same level.
But using the same older version on a different server, I can still create and view the contents of the jobs.
It wasn't me who done the install, something obvioulsy hasn't beein installed in order. I'll try updating the SMSS studio on the new SQL server.
I'll let you all know if that fixes it.
September 16, 2009 at 7:44 am
I copied the IDE folder over to the new server, now SMSS is on the correct version and I can see the contents of the agent jobs now. Bodge job, but it worked.
Thanks for all the help guys.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply