Recovery Model Changing

Hello,
We have numerous SQL Servers that we backup using Veritas Backup Exec with
the SQL Server Agents. We have changed all of our database backup models to
FULL. We backup the transaction logs a few times through the day, and do a
full database backup once a night.
A few of our servers, the msdb database specifically, switch their recovery
mode to simple every once in a while. There doesnt seem to be a time pattern
we can follow, but it does seem to occur after one of our transaction log
backups.
Any ideas if this is a known problem with the msdb database, or if this is
caused by Backup Exec or by SQL Server?
Thanks,
JBaileyThat is the design of SQL Server and the Master and MSDB databases. There
is no real reason to take log backups of these when a Full backup can be
done in a matter of seconds.
--
Andrew J. Kelly
SQL Server MVP
"JBailey" <abc@.123.com> wrote in message
news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> Hello,
> We have numerous SQL Servers that we backup using Veritas Backup Exec with
> the SQL Server Agents. We have changed all of our database backup models
to
> FULL. We backup the transaction logs a few times through the day, and do a
> full database backup once a night.
> A few of our servers, the msdb database specifically, switch their
recovery
> mode to simple every once in a while. There doesnt seem to be a time
pattern
> we can follow, but it does seem to occur after one of our transaction log
> backups.
> Any ideas if this is a known problem with the msdb database, or if this is
> caused by Backup Exec or by SQL Server?
> Thanks,
> JBailey
>|||So do the Master and MSDB databases change infrequently enough that
transaction logs backups are unnecessary?
Thanks,
JBailey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e3b06PzzDHA.3220@.tk2msftngp13.phx.gbl...
> That is the design of SQL Server and the Master and MSDB databases. There
> is no real reason to take log backups of these when a Full backup can be
> done in a matter of seconds.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "JBailey" <abc@.123.com> wrote in message
> news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > We have numerous SQL Servers that we backup using Veritas Backup Exec
with
> > the SQL Server Agents. We have changed all of our database backup models
> to
> > FULL. We backup the transaction logs a few times through the day, and do
a
> > full database backup once a night.
> >
> > A few of our servers, the msdb database specifically, switch their
> recovery
> > mode to simple every once in a while. There doesnt seem to be a time
> pattern
> > we can follow, but it does seem to occur after one of our transaction
log
> > backups.
> >
> > Any ideas if this is a known problem with the msdb database, or if this
is
> > caused by Backup Exec or by SQL Server?
> >
> > Thanks,
> > JBailey
> >
> >
>|||Master rarely changes unless you add a new db or Login. MSDB stores a lot
of history for things like scheduled jobs, backups etc. so it can have
regular changes to it. A lot of people aren't worried as much about loosing
a few hours of history but it is up to each individual to determine that.
What I was getting at though is that to backup Master or MSDB fully usually
only takes a second or two so why bother to schedule log backups. If you
want to back up the db's every hour then just do a full backup. The only
thing it doesn't give you is point in time recovery but again that is rarely
what someone is seeking on these dbs.
--
Andrew J. Kelly
SQL Server MVP
"JBailey" <abc@.123.com> wrote in message
news:udWi7N6zDHA.2396@.TK2MSFTNGP09.phx.gbl...
> So do the Master and MSDB databases change infrequently enough that
> transaction logs backups are unnecessary?
> Thanks,
> JBailey
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e3b06PzzDHA.3220@.tk2msftngp13.phx.gbl...
> > That is the design of SQL Server and the Master and MSDB databases.
There
> > is no real reason to take log backups of these when a Full backup can be
> > done in a matter of seconds.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "JBailey" <abc@.123.com> wrote in message
> > news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > We have numerous SQL Servers that we backup using Veritas Backup Exec
> with
> > > the SQL Server Agents. We have changed all of our database backup
models
> > to
> > > FULL. We backup the transaction logs a few times through the day, and
do
> a
> > > full database backup once a night.
> > >
> > > A few of our servers, the msdb database specifically, switch their
> > recovery
> > > mode to simple every once in a while. There doesnt seem to be a time
> > pattern
> > > we can follow, but it does seem to occur after one of our transaction
> log
> > > backups.
> > >
> > > Any ideas if this is a known problem with the msdb database, or if
this
> is
> > > caused by Backup Exec or by SQL Server?
> > >
> > > Thanks,
> > > JBailey
> > >
> > >
> >
> >
>|||Understood. Thanks for the insight.
JBailey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:urOvXl6zDHA.1688@.TK2MSFTNGP10.phx.gbl...
> Master rarely changes unless you add a new db or Login. MSDB stores a lot
> of history for things like scheduled jobs, backups etc. so it can have
> regular changes to it. A lot of people aren't worried as much about
loosing
> a few hours of history but it is up to each individual to determine that.
> What I was getting at though is that to backup Master or MSDB fully
usually
> only takes a second or two so why bother to schedule log backups. If you
> want to back up the db's every hour then just do a full backup. The only
> thing it doesn't give you is point in time recovery but again that is
rarely
> what someone is seeking on these dbs.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "JBailey" <abc@.123.com> wrote in message
> news:udWi7N6zDHA.2396@.TK2MSFTNGP09.phx.gbl...
> > So do the Master and MSDB databases change infrequently enough that
> > transaction logs backups are unnecessary?
> >
> > Thanks,
> > JBailey
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:e3b06PzzDHA.3220@.tk2msftngp13.phx.gbl...
> > > That is the design of SQL Server and the Master and MSDB databases.
> There
> > > is no real reason to take log backups of these when a Full backup can
be
> > > done in a matter of seconds.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "JBailey" <abc@.123.com> wrote in message
> > > news:eM6$cHyzDHA.1676@.TK2MSFTNGP12.phx.gbl...
> > > > Hello,
> > > >
> > > > We have numerous SQL Servers that we backup using Veritas Backup
Exec
> > with
> > > > the SQL Server Agents. We have changed all of our database backup
> models
> > > to
> > > > FULL. We backup the transaction logs a few times through the day,
and
> do
> > a
> > > > full database backup once a night.
> > > >
> > > > A few of our servers, the msdb database specifically, switch their
> > > recovery
> > > > mode to simple every once in a while. There doesnt seem to be a time
> > > pattern
> > > > we can follow, but it does seem to occur after one of our
transaction
> > log
> > > > backups.
> > > >
> > > > Any ideas if this is a known problem with the msdb database, or if
> this
> > is
> > > > caused by Backup Exec or by SQL Server?
> > > >
> > > > Thanks,
> > > > JBailey
> > > >
> > > >
> > >
> > >
> >
> >
>