Post reply

Migrating SQL Server Jobs

  • March 3, 2004 at 8:37 am

    #84225

    Is there an easy way I can migrate/move SQL server agent jobs from SQL 7.0 server to 2000 server ?

  • March 3, 2004 at 8:45 am

    #496953

    What about other stuff - tables stored procs etc.  Are you not bring this over as well? 

    One method is just to create a script from the job(s) and execute that script in the msdn database on your new server.  This usually works well if you don't have a huge number of jobs to move over.  It can get tedious. 

    Another alternative is to restore the SQL Server 7 msdn database on your SQL Server 2000 server.  Make sure all procs, databases etc referred to in the jobs exist on the SQL Server 2000 box prior to restoring MSDN.  BAck up the 2000 msdn database first.  (I have assumed that no jobs were created on the 2000 box before you do this)

    Francis

  • March 3, 2004 at 8:56 am

    #496966

    We only move selective DB over to the new server. There are approx. 50 jobs been identified need to move to the new servers. Backup/restore msdb may not work, we left a lot of DB behind.

  • March 3, 2004 at 8:58 am

    #496967

    but if you do have A Lot of jobs it is still no biggie. Go to EM -> Management -> SQL Sever Agent -> Jobs, Right click on the JOBS item select All tasks -> Generate SQL Script and your done


    * Noel

  • March 3, 2004 at 9:08 am

    #496971

    In EM right click on the Jobs tab and select All Tasks, Generate SQL Script.  Generate the script and execute in in the msdn database for your new server. 

    Francis

  • March 3, 2004 at 9:11 am

    #496974

    I didn't refresh my screen so I didnt see your post noeld. 

    Francis

  • March 3, 2004 at 9:36 am

    #496983

    Hey no problem, it happens to me all the time


    * Noel

  • March 3, 2004 at 9:45 am

    #496986

    I do try to use EM to generate script, but somehow a lot of job doesn't seems parse the statement correctly. It seems it always mispalce the '. Do any of you have same problem. A lot of our jobs are involved with relative complex SQL statement.

  • March 3, 2004 at 9:58 am

    #496990

    I've had this problem before as well.  Try putting the following before the generated job scripts to see if this fixes things:

     

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    Francis

  • March 3, 2004 at 10:54 am

    #497007

    Doesn't seems to.

  • March 3, 2004 at 1:52 pm

    #497065

    There is a DTS task in SQL2000 to transfer jobs.  The source can be a SQL 7 server and you can select the specific jobs you want to transfer.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 11 posts - 1 through 10 (of 10 total)

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