"Peter Yang [MSFT]" <petery@online.microsoft.com> wrote in message
news:m%23L0Xmk1GHA.400@TK2MSFTNGXA01.phx.gbl...
> Hello Brad,
>
> I understand that you'd like to add database to an task in existing
> maintenance plan in SQL 2005 by SMO.
>
> As for as I know, SMO does not include interface to manage maintenance
> plans. Manitenance plan components is based on SSIS in SQL 2005. The
> maintenance plan feature of SQL Server Database Engine requires components
> of SQL Server 2005 Integration Services (SSIS). To create or execute
> maintenance plans, you must install SSIS.
>
> http://msdn2.microsoft.com/en-us/library/ms187658.aspx
>
> Also:
>
> http://support.microsoft.com/kb/913967/en-us
>
> http://support.microsoft.com/kb/909036/en-us
>
> You could find manitenance plan packages is listed under manitance plan
> entry under msdb folder if you connect to SSIS service in management
> studio. Acutally you could export the package to a dtsx file and edit the
> package in VS 2005. Also, you could import a dtsx package into maintenance
> plan entry so that you could see it under manintenace entry under database
> engine connection.
>
> However, there is no exposed components/interfaces (from SSIS runtime) as
> I
> know that we could develop to edit the pacakge directly via code. BTW, the
> package is stored as binary code in sysdtspackages90 table in msdb
> database. You could see SSIS architeture from the following link:
> http://msdn2.microsoft.com/en-us/library/ms403344.aspx
>
>
> I understand it will be more convenient for to be able to change/edit the
> package by code, and this feedback will be routed to the product team. I
> also encourage you submit via the link below
>
> http://lab.msdn.microsoft.com/productfeedback/default.aspx
>
>
> Since maintance plan is actually SSIS package, you may want to consider
> create package directly by code and schedule to run packages in SQL jobs
> automatically:
>
> http://msdn2.microsoft.com/en-us/library/ms135991.aspx
>
>
> Also, as MVP Hari once mentioned, there is other option to not use the
> maintenance plans. You may consider Write your own maintenance scripts to
> work around the issues. The following links are for your reference:
>
> http://www.dbazine.com/sql/sql-articles/cook10
> http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
>
> If you have futher questions or concerns on this, please feel free to
> let's
> know. I look forward to your comments. Thanks!
>
> Best Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

Brad Baker wrote:
> So basically we either have to manage all maintenance plans by hand or
> completely script full backups, transaction log backups, optimizations and
> integrity checks? Those are some great options Microsoft has left us with.
>

Brad Baker wrote:
>> The backup script that I sent you DOES NOT require you to type in database
>> names.
>
> Based on my understanding utilizing your script would require one of two
> approaches:
>
> 1) Backup all DBS simultaneously - we cannot do this it would take too long.
> I.E. DB backups would run into prime time when the servers are in use.
>
> 2) Setup multiple scripts, type the names of DBs in each script - which is
> error prone and time consuming.

> As previously mentioned the other problem with your scripts is that they do
> not seen to do optimizations or integrity checks. Although I found another

> TSQL script that does do everything here:
> http://www.sqldbatips.com/displaycode.asp?ID=26
>
>
>> Any experienced DBA will tell you that scripts are the way to go.
>
> I'm not a DBA. We don't have a DBA on staff. We probably should, but we
> don't so we have to make due with what we have. So that leaves two options:
>
> 1) Hire a DBA/consultant to code this - which will probably be costly
> 2) Myself or another person on staff has to learn TSQL for the sole purpose
> of generating maintenance plans.

> Neither of the approaches above sounds like a good solution but I guess
> those are the options we've been left with.
>

> The backup script that I sent you DOES NOT require you to type in database
> names.

> Any experienced DBA will tell you that scripts are the way to go.

"Tracy McKibben" <tracy@realsqlguy.com> wrote in message
news:4506E78E.5080400@realsqlguy.com...
> Brad Baker wrote:
>> So basically we either have to manage all maintenance plans by hand or
>> completely script full backups, transaction log backups, optimizations
>> and integrity checks? Those are some great options Microsoft has left us
>> with.
>>
>
> WHY are you so opposed to creating your own maintenance scripts? The
> backup script that I sent you DOES NOT require you to type in database
> names. Even if it did, the fact that it is a script enables you to MODIFY
> IT to suit your needs, something you can't do with the canned tools. Any
> experienced DBA will tell you that scripts are the way to go.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

"Tracy McKibben" <tracy@realsqlguy.com> wrote in message
news:450708EC.6050305@realsqlguy.com...
> Brad Baker wrote:
>>> The backup script that I sent you DOES NOT require you to type in
>>> database names.
>>
>> Based on my understanding utilizing your script would require one of two
>> approaches:
>>
>> 1) Backup all DBS simultaneously - we cannot do this it would take too
>> long. I.E. DB backups would run into prime time when the servers are in
>> use.
>>
>> 2) Setup multiple scripts, type the names of DBs in each script - which
>> is error prone and time consuming.
>
> With minimal effort, my backup script (or ANY backup script) could be
> modified to limit what DB's it touches, like by doing databases starting
> with A-H on Monday, I-Q on Tuesday, R-Z on Wednesday, or whatever you
> want.
>
>> As previously mentioned the other problem with your scripts is that they
>> do not seen to do optimizations or integrity checks. Although I found
>> another
>
> I gave you two scripts, one for backups, one for "optimizations", i.e.
> rebuilding fragmented indexes.
>
>> TSQL script that does do everything here:
>> http://www.sqldbatips.com/displaycode.asp?ID=26
>>
>>
>>> Any experienced DBA will tell you that scripts are the way to go.
>>
>> I'm not a DBA. We don't have a DBA on staff. We probably should, but we
>> don't so we have to make due with what we have. So that leaves two
>> options:
>>
>> 1) Hire a DBA/consultant to code this - which will probably be costly
>> 2) Myself or another person on staff has to learn TSQL for the sole
>> purpose of generating maintenance plans.
>
> I'm not a mechanic, but I did learn to change my own oil and do basic car
> maintenance so I'm not dependent on my car dealership. If you're writing
> applications that run against SQL Server, shouldn't you know a bit about
> SQL?
>
>
>> Neither of the approaches above sounds like a good solution but I guess
>> those are the options we've been left with.
>>
>
> I guess all I can say is "Good luck"...
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com