How to include more databases in DB Mainteance Plan for SQL Server 2005

Hi,
I have created a DB maintenance plan by using wizard to backup a production
database daily.
There is a request to include 2 more databases to be included in the
maintenance plan. I select the maintenance plan and press "Modify" but I am
not able to work out how to include 2 more databases. Besides, it appears
that both transaction log & database backup are not deleted since the plan
is executed, is there something wrong ?
Is it possible to give me some advice ?
ThanksPeter
You asked the question yesterday and I gave you the answer. Don't you
rememner that?
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have created a DB maintenance plan by using wizard to backup a
> production database daily.
> There is a request to include 2 more databases to be included in the
> maintenance plan. I select the maintenance plan and press "Modify" but I
> am not able to work out how to include 2 more databases. Besides, it
> appears that both transaction log & database backup are not deleted since
> the plan is executed, is there something wrong ?
> Is it possible to give me some advice ?
> Thanks
>|||> There is a request to include 2 more databases to be included in the maintenance plan. I
select
> the maintenance plan and press "Modify" but I am not able to work out how
to include 2 more
> databases.
You need to add the databases to the backup task inside the plan. Right-lock
the backup task, select
"Edit..." and in the "Databases:" drop.down you select the databases you wan
t to include.

> Besides, it appears that both transaction log & database backup are not de
leted since the plan is
> executed, is there something wrong ?
Add a "Maintenance Cleanup Task" to the plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have created a DB maintenance plan by using wizard to backup a productio
n database daily.
> There is a request to include 2 more databases to be included in the maint
enance plan. I select
> the maintenance plan and press "Modify" but I am not able to work out how
to include 2 more
> databases. Besides, it appears that both transaction log & database backu
p are not deleted since
> the plan is executed, is there something wrong ?
> Is it possible to give me some advice ?
> Thanks
>|||Dear Uri,
I forget to mention that it is SQL Server 2005 yesterday. In this way, your
advice is applicable for SQL Server 2000.
Thanks
Peter
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uEfBDB1IHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Peter
> You asked the question yesterday and I gave you the answer. Don't you
> rememner that?
>
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
>|||Dear Tibor and Uri,
Thank you for your advice.
I find that the reason why I am not able to add more databases in the task
is because I use SA in my workstation while Windows Authentication is used
at the server side. In this way, I change the connection from Windows
Authentication to SQL Authentication for both local and target servers and I
am able to do it on my workstation. I have changed the ownership of the
jobs to SA (Instead of Administrator).
I would like to seek your advice
1) Is it possible to change the ownership of the "Database Maintenance Plan"
from Administrator to SA ?
2) Is it necessary to add the "Cleanup Task" for the daily transaction log
maintenance plan so that old transaction log backup will be deleted ?
3) Which step should be performed first - Cleanup Task or Backup Task ?
Should the constraint be success or finish ?
4) Is it necessary to add another task to delete old reports in the "Weekly
Maintenance Plan" so that the logging will be deleted ?
Thanks
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O4yE4B1IHHA.2236@.TK2MSFTNGP02.phx.gbl...
> You need to add the databases to the backup task inside the plan.
> Right-lock the backup task, select "Edit..." and in the "Databases:"
> drop.down you select the databases you want to include.
>
> Add a "Maintenance Cleanup Task" to the plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:u01Fh30IHHA.4712@.TK2MSFTNGP04.phx.gbl...
>|||> 1) Is it possible to change the ownership of the "Database Maintenance Plan" from Administ
rator to
> SA ?
I would guess that you would change the owner of the job. To the best of my
knowledge, a maint plan
doesn't have an owner, the job does. Not sure, though.

> 2) Is it necessary to add the "Cleanup Task" for the daily transaction log
maintenance plan so
> that old transaction log backup will be deleted ?
Yes, if you want the old backup files to be removed and if you don't remove
them some other way.

> 3) Which step should be performed first - Cleanup Task or Backup Task ? Sh
ould the constraint be
> success or finish ?
This is really your decision. I prefer to do the backup first, and if it fai
ls I don't remove old
backups.

> 4) Is it necessary to add another task to delete old reports in the "Weekl
y Maintenance Plan" so
> that the logging will be deleted ?
Yes, if you want the old report files to be removed and if you don't remove
them some other way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%23gNfQpBJHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Dear Tibor and Uri,
> Thank you for your advice.
> I find that the reason why I am not able to add more databases in the task
is because I use SA in
> my workstation while Windows Authentication is used at the server side. I
n this way, I change the
> connection from Windows Authentication to SQL Authentication for both loca
l and target servers and
> I am able to do it on my workstation. I have changed the ownership of the
jobs to SA (Instead of
> Administrator).
> I would like to seek your advice
> 1) Is it possible to change the ownership of the "Database Maintenance Pla
n" from Administrator to
> SA ?
> 2) Is it necessary to add the "Cleanup Task" for the daily transaction log
maintenance plan so
> that old transaction log backup will be deleted ?
> 3) Which step should be performed first - Cleanup Task or Backup Task ? Sh
ould the constraint be
> success or finish ?
> 4) Is it necessary to add another task to delete old reports in the "Weekl
y Maintenance Plan" so
> that the logging will be deleted ?
> Thanks
> Peter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:O4yE4B1IHHA.2236@.TK2MSFTNGP02.phx.gbl...
>|||Dear Tibor,
Thank you for your advice.
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejNfWICJHHA.3424@.TK2MSFTNGP02.phx.gbl...
> I would guess that you would change the owner of the job. To the best of
> my knowledge, a maint plan doesn't have an owner, the job does. Not sure,
> though.
>
> Yes, if you want the old backup files to be removed and if you don't
> remove them some other way.
>
> This is really your decision. I prefer to do the backup first, and if it
> fails I don't remove old backups.
>
> Yes, if you want the old report files to be removed and if you don't
> remove them some other way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:%23gNfQpBJHHA.4000@.TK2MSFTNGP06.phx.gbl...
>