Monday, March 19, 2012

Errors of System DB Backup T-logJobs for Maint Plan

I have been bothered with the error signs of the DB Backup
jobs for my DB Maint Plan. These job errors were shown in
SQL Server Agent node of the Enterprise Manager.
After checking the DB Maint Plan history, I found the
errors were due to the Backup T-log job errors on system
databases, e.g. master, msdb. Later on I found out that
we can set master to Full recovery model, but still no T-
log backup choice available, while tempdb can not even be
set to Full model.
If I still want to select 'All Databases' in my Maint
Plan, how can I avoid these Maint Plan job errors in the
SQL Server Agent node? How do I decide the DB recovery
model (Full or Simple) for the system databases in my
database servers when configuring my Maint Plan?
Furthermore, I have heard that there are no T-log backup
device for the system databases, only for user databases.
What exactly does this mean?
Thanks in Advance.
JeffreyI normally create one maint plan for my sysdbs this way you can avoid that
and some other errors.
You cannot restore the master database like you restore the users db, the
master db has to be rebuild. Because of the size of your system db i
normally backed them up twice a day, and overwrite every two weeks and i
have it set to simple.
Take a look at BOL (books online) to understand more about backup models.
Yovan Fernandez
"Jeffrey" <jwang@.dot.state.tx.us> wrote in message
news:0bd901c377d7$bcf03a40$a001280a@.phx.gbl...
> I have been bothered with the error signs of the DB Backup
> jobs for my DB Maint Plan. These job errors were shown in
> SQL Server Agent node of the Enterprise Manager.
> After checking the DB Maint Plan history, I found the
> errors were due to the Backup T-log job errors on system
> databases, e.g. master, msdb. Later on I found out that
> we can set master to Full recovery model, but still no T-
> log backup choice available, while tempdb can not even be
> set to Full model.
> If I still want to select 'All Databases' in my Maint
> Plan, how can I avoid these Maint Plan job errors in the
> SQL Server Agent node? How do I decide the DB recovery
> model (Full or Simple) for the system databases in my
> database servers when configuring my Maint Plan?
> Furthermore, I have heard that there are no T-log backup
> device for the system databases, only for user databases.
> What exactly does this mean?
> Thanks in Advance.
> Jeffrey
>

No comments:

Post a Comment