It's possible to create a maintenance plan with Enterprise manager

It's possible to create a maintenance plan with Enterprise manager in my MSDE
db?
When I try to create a "maintenance plan" I have blank menu (for example
"Use this directory" or "remove files older than" or "backup file extension"
).
Any help will be appreciated.
Thanks
hi Andrea,
andrea favero wrote:
> It's possible to create a maintenance plan with Enterprise manager in
> my MSDE db?
> When I try to create a "maintenance plan" I have blank menu (for
> example "Use this directory" or "remove files older than" or "backup
> file extension" ).
> Any help will be appreciated.
> Thanks
yes it is... the produced maintenance plan will result in a number of jobs
scheduled using SQL Server Agent scheduler...
at schedule occuring, the Job will execute the included job step(s) calling
the maintenance plan runtime and it will be processed...
the properties you are specifying regard "where do you want the backup file
to be stored", so you have to provide the full path of the database backups
browsing (or typing) the actual folder from the SQL Server OS point of
view..
"remove files older than" specifies to delete old backups (older then N
minutes/hours/days/etc :D), but probably a missing piece in MSDE package
does not fill those properties automatically (and you wont be able to set
this manually)
"backup file extension" means what the default extension will be for the
created physical files... again, probably a missing piece in MSDE package
does not fill those properties automatically..
you could probably go on and later manually edit the job step, where a call
to xp_sqlmaint is performed in the way
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
4BDC174A-0C83-4B10-9054-D7388950FF8F -Rpt "C:\Programmi\Microsoft SQL
Server\MSSQL\LOG\DB Maintenance
Plan14.txt" -WriteHistory -VrfyBackup -BkUpMedia
ISK -BkUpDB -UseDefDir -DelBkUps 0 -BkExt "bak"'
and so modifying the -DelBkUps 0 in
-DelBkUps 1days or whatever... but you have to manually dig in the sqlmaint
parameter list
(http://msdn.microsoft.com/library/de...maint_19ix.asp)
personally I do not like SQL Server 7.0 and 2000 maintenance plan utility
and I strongly prefer writing my job by hand specifying the actual
statements that will be perfomed instead of assuming the black box (and
buggy) of maintenance plan will do what I expect to be done...
SQL Server 2005 modified that behavior, but SQLExpress is not provided with
the maintenance plan runtime and with the SQLServer Agent anymore, thus you
have to resort on third party (or home made) scheduler or on the native OS
AT scheduler like in http://www.sqldbatips.com/showarticle.asp?ID=29
BTW, as MSDE usuer, you are not likely supposed to legally interact with it
via Enterprise Manager or Query Analyzer..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Issues with Transaction Log backups

I'm pretty new to SQL, and am running into a problem. I had setup a
Maintenance plan on one of my databases and the Transaction Log Backups are
not working. I'm not getting any feedback in the error on why they are not
working. This is the error that I get:
JOB RUN: 'Transaction Log Backup Job for DB Maintenance Plan 'CRMDEV DB
Maintenance Plan'' was run on 9/7/2006 at 5:30:00 PM
DURATION: 0 hours, 0 minutes, 0 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 2 (Schedule 1).
The last step to run was step 1 (Step 1).
Does anyone know what could be causing the error? I first thought it was
because of the account assigned to run the job, so I switched it from the
administrator to the SA account. But, still fails. Any suggestions on what
could be going on would be appreciated...My daily backups configured in the
Maintenance plan of the database are working though.
Thank you, Sara
Thanks!Saral6978 wrote:
> I'm pretty new to SQL, and am running into a problem. I had setup a
> Maintenance plan on one of my databases and the Transaction Log Backups are
> not working. I'm not getting any feedback in the error on why they are not
> working. This is the error that I get:
> JOB RUN: 'Transaction Log Backup Job for DB Maintenance Plan 'CRMDEV DB
> Maintenance Plan'' was run on 9/7/2006 at 5:30:00 PM
> DURATION: 0 hours, 0 minutes, 0 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 2 (Schedule 1).
> The last step to run was step 1 (Step 1).
> Does anyone know what could be causing the error? I first thought it was
> because of the account assigned to run the job, so I switched it from the
> administrator to the SA account. But, still fails. Any suggestions on what
> could be going on would be appreciated...My daily backups configured in the
> Maintenance plan of the database are working though.
> Thank you, Sara
> Thanks!
>
>
Are you possibly trying to do a transaction log backup on a database
that is in Simple recovery mode?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I guess I'm not sure...how would I know that?
"Tracy McKibben" wrote:
> Saral6978 wrote:
> > I'm pretty new to SQL, and am running into a problem. I had setup a
> > Maintenance plan on one of my databases and the Transaction Log Backups are
> > not working. I'm not getting any feedback in the error on why they are not
> > working. This is the error that I get:
> >
> > JOB RUN: 'Transaction Log Backup Job for DB Maintenance Plan 'CRMDEV DB
> > Maintenance Plan'' was run on 9/7/2006 at 5:30:00 PM
> > DURATION: 0 hours, 0 minutes, 0 seconds
> > STATUS: Failed
> > MESSAGES: The job failed. The Job was invoked by Schedule 2 (Schedule 1).
> > The last step to run was step 1 (Step 1).
> >
> > Does anyone know what could be causing the error? I first thought it was
> > because of the account assigned to run the job, so I switched it from the
> > administrator to the SA account. But, still fails. Any suggestions on what
> > could be going on would be appreciated...My daily backups configured in the
> > Maintenance plan of the database are working though.
> >
> > Thank you, Sara
> >
> > Thanks!
> >
> >
> >
> Are you possibly trying to do a transaction log backup on a database
> that is in Simple recovery mode?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Saral6978 wrote:
> I guess I'm not sure...how would I know that?
>
Check the database properties of each database you're attempting to back
up. If the recovery model is "Simple", you can't (nor do you need to)
run a transaction log backup against that database. Consult Books
Online to learn what the various recovery models (Simple, Full,
Bulk-Logged) mean and what they offer.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you, Tracy. The recovery model IS Simple...I will be taking a 2day
course on SQL in the next month, so I'm sure that will help too. Thank you
so much for your input.
"Tracy McKibben" wrote:
> Saral6978 wrote:
> > I guess I'm not sure...how would I know that?
> >
> Check the database properties of each database you're attempting to back
> up. If the recovery model is "Simple", you can't (nor do you need to)
> run a transaction log backup against that database. Consult Books
> Online to learn what the various recovery models (Simple, Full,
> Bulk-Logged) mean and what they offer.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Saral6978 wrote:
> Thank you, Tracy. The recovery model IS Simple...I will be taking a 2day
> course on SQL in the next month, so I'm sure that will help too. Thank you
> so much for your input.
>
No problem. I won't go into my rant about the maintenance plan wizards
allowing you to do this to yourself... After you finish your course,
you should learn how to write your own maintenance scripts without
relying on those wizards.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Aww, c'mon Tracy. We love your rants. ;)
To the poster, I found that the SQL Server Unleashed book is invaluable
as a resource. I picked it up on Ebay for under $30 and I use it
nearly every day.
Tracy McKibben wrote:
> Saral6978 wrote:
> > Thank you, Tracy. The recovery model IS Simple...I will be taking a 2day
> > course on SQL in the next month, so I'm sure that will help too. Thank you
> > so much for your input.
> >
> No problem. I won't go into my rant about the maintenance plan wizards
> allowing you to do this to yourself... After you finish your course,
> you should learn how to write your own maintenance scripts without
> relying on those wizards.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Issues with Transaction Log backups

I'm pretty new to SQL, and am running into a problem. I had setup a
Maintenance plan on one of my databases and the Transaction Log Backups are
not working. I'm not getting any feedback in the error on why they are not
working. This is the error that I get:
JOB RUN: 'Transaction Log Backup Job for DB Maintenance Plan 'CRMDEV DB
Maintenance Plan'' was run on 9/7/2006 at 5:30:00 PM
DURATION: 0 hours, 0 minutes, 0 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 2 (Schedule 1).
The last step to run was step 1 (Step 1).
Does anyone know what could be causing the error? I first thought it was
because of the account assigned to run the job, so I switched it from the
administrator to the SA account. But, still fails. Any suggestions on what
could be going on would be appreciated...My daily backups configured in the
Maintenance plan of the database are working though.
Thank you, Sara
Thanks!Saral6978 wrote:
> I'm pretty new to SQL, and am running into a problem. I had setup a
> Maintenance plan on one of my databases and the Transaction Log Backups ar
e
> not working. I'm not getting any feedback in the error on why they are no
t
> working. This is the error that I get:
> JOB RUN: 'Transaction Log Backup Job for DB Maintenance Plan 'CRMDEV DB
> Maintenance Plan'' was run on 9/7/2006 at 5:30:00 PM
> DURATION: 0 hours, 0 minutes, 0 seconds
> STATUS: Failed
> MESSAGES: The job failed. The Job was invoked by Schedule 2 (Schedule 1).
> The last step to run was step 1 (Step 1).
> Does anyone know what could be causing the error? I first thought it was
> because of the account assigned to run the job, so I switched it from the
> administrator to the SA account. But, still fails. Any suggestions on wh
at
> could be going on would be appreciated...My daily backups configured in th
e
> Maintenance plan of the database are working though.
> Thank you, Sara
> Thanks!
>
>
Are you possibly trying to do a transaction log backup on a database
that is in Simple recovery mode?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I guess I'm not sure...how would I know that?
"Tracy McKibben" wrote:

> Saral6978 wrote:
> Are you possibly trying to do a transaction log backup on a database
> that is in Simple recovery mode?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Saral6978 wrote:
> I guess I'm not sure...how would I know that?
>
Check the database properties of each database you're attempting to back
up. If the recovery model is "Simple", you can't (nor do you need to)
run a transaction log backup against that database. Consult Books
Online to learn what the various recovery models (Simple, Full,
Bulk-Logged) mean and what they offer.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you, Tracy. The recovery model IS Simple...I will be taking a 2day
course on SQL in the next month, so I'm sure that will help too. Thank you
so much for your input.
"Tracy McKibben" wrote:

> Saral6978 wrote:
> Check the database properties of each database you're attempting to back
> up. If the recovery model is "Simple", you can't (nor do you need to)
> run a transaction log backup against that database. Consult Books
> Online to learn what the various recovery models (Simple, Full,
> Bulk-Logged) mean and what they offer.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Saral6978 wrote:
> Thank you, Tracy. The recovery model IS Simple...I will be taking a 2day
> course on SQL in the next month, so I'm sure that will help too. Thank yo
u
> so much for your input.
>
No problem. I won't go into my rant about the maintenance plan wizards
allowing you to do this to yourself... After you finish your course,
you should learn how to write your own maintenance scripts without
relying on those wizards.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Aww, c'mon Tracy. We love your rants. ;)
To the poster, I found that the SQL Server Unleashed book is invaluable
as a resource. I picked it up on Ebay for under $30 and I use it
nearly every day.
Tracy McKibben wrote:
> Saral6978 wrote:
> No problem. I won't go into my rant about the maintenance plan wizards
> allowing you to do this to yourself... After you finish your course,
> you should learn how to write your own maintenance scripts without
> relying on those wizards.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com