Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    5

    Question Unanswered: Transfer maintenance jobs between servers

    Hi there,

    I am in the process of movins a database from an old server to a new one. Is there any way to move the maintenance jobs that I created on the old server? What about the local packages from DTS.

    Thanks a lot,
    poli

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I assume you're talking about moving DB Maintenance Plan-based jobs. You'll need to query the following tables in MSDB:

    sysdbmaintplans
    sysdbmaintplan_databases

    Basically, you need to insert into those tables on the new server the values from the old one. Then you just script your existing scheduled tasks. Once you got the script, alter the call to sp_add_jobstep by changing the -PlanID switch to -PlanName and reference the DB Maintenance plan by name.

    For DTS packages you can use DTSBackup2000 tool.
    Here's the link:

    http://www.sqldts.com/default.aspx?220,272,272,1,1

    Hope it helps.

  3. #3
    Join Date
    Aug 2003
    Posts
    5
    Thanks a lot for the link. The program worked great.

    You are right; I meant DB Maintenance Plan-based jobs. Is there any tool that does the transfer automatically? I'm not very proficient in SQL and I'd hate to mess up something.

    Thanks,
    poli

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Make sure to test it thoroughly before using:

    if object_id('dbo.sp_ScriptMaintenancePlans') is not null and objectproperty(object_id('dbo.sp_ScriptMaintenance Plans'), 'IsProcedure') = 1
    drop procedure dbo.sp_ScriptMaintenancePlans
    go
    /*
    ************************************************** ********************************
    Created : 08/14/2003
    By : Robert Djabarov
    Purpose : Generate a script for existing DB Maintenance plans in order to re-
    create them on a different server.

    DISCLAIMER:
    AS USUSAL, USE AT YOUR OWN RISK, NO WARRANTIES AND/OR LIABILITIES FOR
    DAMAGED SYSTEMS.
    ************************************************** ********************************
    */
    create procedure dbo.sp_ScriptMaintenancePlans as
    declare @PlanID nchar(36), @print varchar(8000), @crlf char(2)
    declare
    @plan_name nvarchar (128),
    @date_created datetime,
    @owner nvarchar (128),
    @max_history_rows int,
    @remote_history_server nvarchar (128),
    @max_remote_history_rows int,
    @user_defined_1 int,
    @user_defined_2 nvarchar (100),
    @user_defined_3 datetime,
    @user_defined_4 nchar(36),
    @database_name varchar(128)

    set @crlf = char(13) + char(10)

    select @PlanID = min(cast(plan_id as nchar(36))) from msdb.dbo.sysdbmaintplans (nolock)
    while @PlanID is not null begin
    select
    @plan_name = plan_name ,
    @date_created = date_created ,
    @owner = owner ,
    @max_history_rows = max_history_rows ,
    @remote_history_server = remote_history_server ,
    @max_remote_history_rows= max_remote_history_rows ,
    @user_defined_1 = user_defined_1 ,
    @user_defined_2 = user_defined_2 ,
    @user_defined_3 = user_defined_3 ,
    @user_defined_4 = user_defined_4
    from msdb.dbo.sysdbmaintplans (nolock)
    print '--Script for Plan ' + @PlanID + ' - ' + @plan_name
    set @print = 'if exists(select 1 from msdb.dbo.sysdbmaintplans (nolock) where (plan_id = ' + char(39) + @PlanID + char(39) + ' or plan_name = ' + char(39) + @plan_name + char(39) + ') begin '
    set @print = @print + @crlf
    set @print = @print + ' delete d from msdb.dbo.sysdbmaintplan_databases d ' + @crlf
    set @print = @print + ' inner join msdb.dbo.sysdbmaintplans p (nolock) ' + @crlf
    set @print = @print + ' on d.plan_id = p.plan_id ' + @crlf
    set @print = @print + ' where (p.plan_id = '''
    set @print = @print + @PlanID + ''''
    set @print = @print + ' or p.plan_name = ' + char(39) + @plan_name + char(39) + ')'
    set @print = @print + @crlf
    set @print = @print + ' delete msdb.dbo.sysdbmaintplans where (plan_id = ' + char(39) + @PlanID + char(39) + ' or plan_name = ' + char(39) + @plan_name + char(39) + ')'
    set @print = @print + @crlf + 'end' + @crlf + 'go'

    print @print

    set @print = 'insert msdb.dbo.sysdbmaintplans (' + @crlf
    set @print = @print + ' plan_id, plan_name, date_created, owner, max_history_rows, remote_history_server, max_remote_history_rows, user_defined_1, user_defined_2, user_defined_3, user_defined_4) ' + @crlf
    set @print = @print + 'values (' + char(39) + @PlanID + char(39) + ', '
    set @print = @print + char(39) + @plan_name + char(39) + ', ' + char(39)
    set @print = @print + convert(char(10), @date_created, 101) + ' ' +
    convert(varchar(10), @date_created, 114) + char(39) + ', '
    set @print = @print + isnull(char(39) + @owner + char(39), 'NULL') + ', '
    set @print = @print + isnull(cast(@max_history_rows as varchar(10)), 'NULL') + ', '
    set @print = @print + isnull(char(39) + @remote_history_server + char(39), 'NULL') + ', '
    set @print = @print + isnull(cast(@max_remote_history_rows as varchar(10)), 'NULL') + ', '
    set @print = @print + isnull(cast(@user_defined_1 as varchar(10)), 'NULL') + ', '
    set @print = @print + isnull(char(39) + @user_defined_2 + char(39), 'NULL') + ', '
    set @print = @print + isnull(char(39) + convert(char(10), @date_created, 101) + ' ' + convert(varchar(10), @date_created, 114) + char(39), 'NULL') + ', '
    set @print = @print + isnull(char(39) + @user_defined_4 + char(39), 'NULL') + ')' + @crlf + 'go' + @crlf

    print @print

    select @database_name = min(database_name) from msdb.dbo.sysdbmaintplan_databases (nolock) where cast(plan_id as nchar(36)) = @PlanID
    while @database_name is not null begin
    set @print = 'if exists (select 1 from master.dbo.sysdatabases (nolock) where [name] = ' + char(39) + @database_name + char(39) + ')'
    set @print = @print + @crlf + ' insert msdb.dbo.sysmaintplan_databases (plan_id, database_name) values (' + @crlf
    set @print = @print + ' ' + char(39) +
    @PlanID + char(39) + ', ' + char(39) + @database_name + char(39) + ')' + @crlf
    set @print = @print + 'else' + @crlf
    set @print = @print + ' print ' + char(39) +
    'Database <' + @database_name + '> was not found on the server!' + char(39) + @crlf + 'go' + @crlf

    print @print

    select @database_name = min(database_name) from msdb.dbo.sysdbmaintplan_databases (nolock) where cast(plan_id as nchar(36)) = @PlanID and database_name > @database_name
    end
    print ''
    select @PlanID = min(cast(plan_id as nchar(36))) from msdb.dbo.sysdbmaintplans (nolock) where cast(plan_id as nchar(36)) > @PlanID
    end
    go

  5. #5
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Of course if you don't use maint.exe then you don't have this problem (and have a lot more control over what is going on).

  6. #6
    Join Date
    Aug 2003
    Posts
    5

    Smile

    Thanks a lot for your help. I'll give it a try on my test server.
    poli

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •