August 6, 2012 at 1:46 pm
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
August 6, 2012 at 2:24 pm
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.
August 7, 2012 at 2:42 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply