12

I created a maintenance plan for my SQL Server 2008 R2 using SQL Server Management Studio (SSMS).

However, I am unable to export/convert it to a script, in case I need to redo it on another server.

Is there a way?

If no, will there be a way with a newer version of SQL Server?

  • 1
    You may wish to look at Ola Hallengren's maintenance scripts. They support far more options and configurability, plus they are standard T-SQL so you can customize them, and script the objects for migration to other servers as necessary. ola.hallengren.com – Max Vernon Aug 18 '14 at 14:48
10

When you create job using maintenance plan, a job is automatically created in SQL Server Agent under Jobs. So now go to SQL Server Agent, expand jobs, and look for your job. Right click on it and select Script job as... Drop and recreate to... New query editor window.

Edit:

You can export the maintenance plans as well. You need to connect to Integration Services of the instance. Expand the stored packages then MSDB then expand Maintenance plans. Right click on MP and select a export package. Then you can move the package to another SQL Server.

  • 3
    Just clarifying - Maintenance plans are actually stored as SSIS packages and the agent job that executes them actually calls the packages to execute. It's stored in MSDB, but there is no single click way to get the actual maintenance plan as a script as it's an SSIS package. It is possible to export the package, but not through the GUI. – Sean says Remove Sara Chipps Aug 18 '14 at 12:33
  • 2
    Agree with sean here. @ Carlo: The script would be that of job created not of the maintenance plan. When you will run the script on different server just the job would be created not MP. – Shanky Aug 18 '14 at 13:11
0

Fastest way to move plans between servers is to connect from Management studio to server where plans are already confugured, start editing your plan and just copy\paste tasks between subplans. Though, this only works when connection between servers is working.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.