April 9, 2003 at 5:20 am
Hi,
Does anyone know if it is possible to export a maintenance plan to a script so that it can be run on another server?
Thanks in advance for your time.
April 9, 2003 at 5:58 am
I don't know of such a possibility.
You can scripts jobs and transfer DTS packages instead.
To transfer DTS packages please refer to:
http://www.sqldts.com/default.aspx?6,105,204,0,1
Kind regards.
Franco
Franco
April 9, 2003 at 7:32 am
What I do is edit my package and if I don't have access directly to the other server, I then say "SAVE AS" and store it as a "Structured Storage File" on a floppy, or CD. Then take the floppy or CD over to the other server and on the "Data Transformation Services" right click and "Open Package". The other way if you have access to the other server is when editing the package, use "SAVE AS" to save the package at location "SQL Server" and then enter the server name where you want to save it.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
April 9, 2003 at 7:34 am
Each job created by maintenance plan has planid attached and you can't simply script it, transfer and run it in another server.
Maybe you could try Profiler to trace the exact SQL statements when you create maintance paln.
April 9, 2003 at 7:47 am
Ok, now I've had my coffee. Ignore my last post.
Yes it might be true you can't build the script, although it is really easy to do it manually, and if I needed to move maintenance plans around it would also be easy to build a routine to do this. Maybe someday I will build this util. Here are the steps to moving a maintenance plan manually.
1) Run command on source server
execute sp_help_maintenance_plan
2) From the output determine which plan_id you wish to generate, then issue the following command on the source server replaceing <plan_id> with the plan you are thinking about generating:
execute sp_help_maintenance_plan '<plan_id>'
This command should identify the databases that the plan is associated with. Note the databases and SQL Server agent jobs associated with this plan.
3) Generate the script for the SQL Server agent jobs from the source server.
4) On the target server create the all the SQL Server agent jobs for the maintanenance job
5) Create the maintenance plan on the target server by issuing the following commands:
DECLARE @myplan_id UNIQUEIDENTIFIER
EXECUTE sp_add_maintenance_plan N'Myplan',@plan_id=@myplan_id OUTPUT
print @myplan_id
Note the plan_id that was created.
6) Isssue the following command on the target server, one for each database identified in step 2:
Execute sp_add_maintenance_plan_db <plan_id>',N'<database>'
7) Then execute the following on the target server, once for every job created.
EXECUTE sp_add_maintenance_plan_job N'<plan_id>', N'<job_id>'
Ok, I never said it was going to be easy.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
April 10, 2003 at 3:33 am
Would it be possible to elaborate points 3, 4 and 5. I am not sure how to do them.
Thanks again,
Roy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply