August 8, 2011 at 9:36 am
I work for a company where I'm regularly setting up new DB boxes, including setup of maintenance plans and configuring database mail. I've tried, unsuccessfully, to find a way to automate the creation of the maintenance plans. I do have several scripts I run to setup alerts and mail.
I'm wondering if I can just restore an msdb backup as a way to get this all done in one quick step. Will that work?
August 8, 2011 at 10:02 am
You'll restore a lot more than just jobs, and I wouldn't recommend this. The msdb restore is really for DR purposes. I suspect it would be OK, but I wouldn't want to break anything down the road.
Can you not just script out the mp (save as a file) and script the jobs and run one big script?
Personally if you are looking to automate things, I wouldn't necessarily use the maintenance plans. You don't necessarily get a lot with them, and there are lots of scripts on sites like this that do the same thing and you could more easily then deploy a script of changes.
August 8, 2011 at 10:55 am
I've been unable to find a way to script out MP's. I do like the extended logging information the MP's provide. The closest thing I've been able to find for automating creation of MP's is to export/import SSIS packages, but I really haven't had much luck with that.
I'm talking about a vanilla msdb database with only my scripts, database mail, and maintenance plans being restored to another vanilla install.
1 db restore vs. execution of several scripts is what I'm looking at. If the msdb restore route is safe, it'll be quicker too.
August 8, 2011 at 11:06 am
There are a few items listed in this thread to watch out for, or that will cause issues: http://www.sqlservercentral.com/Forums/Topic501078-5-1.aspx
However, if you have to go through the restore, I'm not sure that's more complicated than running a script to create procs, and add jobs. It should be one package that you run once to set things up. I used to deploy a whole admin database, including jobs to run and handle backups, from one script (series of batches) that built administrative items for a new install.
August 8, 2011 at 11:50 am
Yeah, that "Local server connection" part is a PITA.
Maybe I will just stop using maintenance plans altogether, they seem to be more trouble than they are worth.
Thanks for the comments!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply