Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


copy maintenance plan


copy maintenance plan

Author
Message
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2045 Visits: 3862
We have quite a few SQL servers in our enviroment. And we setup standard maintenance plan with 5 subplans like backups( include full, diffrential, transaction log), weekly reorg, check db integrity. clean up history etc.

I usually manually created this maintenance plans for each server.

I wonder if there is an easier way to migrate or copy it to new server.

I know I can copy msdb package to the server, but it also need multiple steps, and need to change server connection, I found it saves more time to recreate them manually in ssms than using the import package.

I don't find an option that I can script out these plans.

Thanks
Scott D. Jacobson
Scott D. Jacobson
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 1000
There are a couple options here. Remember, that since SQL 2005, Maintenance Plans are basically just SSIS packages. The wizard just provides an interface with limited SSIS actions inside SSMS.

1) Create your plan inside BIDS or Visual Studio. This gives you the power of treating it like an actual SSIS package. You can modify the connection objects each time before you deploy to a new server. Of course, this could be a problem since now you need to either copy the package to the server every time you want to update or deploy it. You may also need to load it into Integration Services through SSMS to store it MSDB if that's your thing. The latter part isn't required but I know some people like to manage their packages this way.

2) Instead of using the wizard, create a DBA database on each server. Script out your plan actions as pure TSQL. Create stored procedures with the TSQL inside the DBA database. Run each inside an agent job step. This way, the jobs can be scripted. Once the DBA database is created on each server, you can run the job creation script. Then all you need to do is schedule the job(s) and you're done. The schedules could also be scripted.

The 2nd is probably your best bet if you want something scriptable, portable and very re-useable. If you're not terribly familiar with system objects and/or TSQL this is a good way to hone those skills.

Of course, if your plans are very simple, you may just be better off continuing with the wizard for now. I still suggest you take a look at the TSQL being executed by your plans now to better acquaint yourself with how to execute these tasks with nothing but TSQL.
heymiky
heymiky
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 Visits: 844
If you haven't already read it take a look at this http://ola.hallengren.com/

I use the MaintenanceSolution.sql on there for just about everything with a few changes here and there
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search