December 5, 2008 at 12:03 pm
hello
how to transfer maintenance plans from one instance to other in sql server 2000?
December 6, 2008 at 4:56 am
[font="Verdana"]I found this while browsing couple of days ago. Did not test it but, I hope it helps.
Original Location:
http://www.dbforums.com/archive/index.php/t-883004.html
Request you to, not remove the Author comments on top. If you make any changes, add a line in the 'Modified by' part and detail the changes made below.
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.
Modified by:
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 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
[/font]
December 8, 2008 at 12:48 pm
I've never had good luck doing this. The script might work, but honestly, recreating them isn't that bad, even on dozens of instances and it's a one time task.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply