Monday, February 20, 2012

Master, Model, MSDB What Recovery Models

What Recovery Model and what Backup Type should be used on the Master, Model
and MSDB databases?Master and MSDB no choice as they get reset to simple anyway. Regular
(daily) full backups tends to be the most common.
Model - matters little but it does set the default recovey model for new
databses being created.
Mike John
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:70327943-6C4C-46B0-98AD-C875FCD7F9D1@.microsoft.com...
> What Recovery Model and what Backup Type should be used on the Master,
> Model
> and MSDB databases?|||Hmmmm
1. Are you saying that that the Master and MSDB will always stay in simple
no matter what I pick?
2. If I do a full back up of the Master and MSDB every night and the
recovery mode is locked to simple, the Transaction Log that comes with the
full is not really used, it just seems to restore to the last "static" state.
"Mike John" wrote:
> Master and MSDB no choice as they get reset to simple anyway. Regular
> (daily) full backups tends to be the most common.
> Model - matters little but it does set the default recovey model for new
> databses being created.
> Mike John
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:70327943-6C4C-46B0-98AD-C875FCD7F9D1@.microsoft.com...
> > What Recovery Model and what Backup Type should be used on the Master,
> > Model
> > and MSDB databases?
>
>|||You can have msdb in full recovery mode. I usually to log backups for msdb. The problem is that
Agent will set msdb to simple on startup. But you can create a job that reverses this and set the
job as a startup job.
You can set master to full, but that is a "null" operation as behavior is still same as simple and
you still cannot do log backups. I'd prefer an error message if I (try) to set master to full.
The transaction log is definitely used in simple recovery mode, for things as rollbacks and recovery
on startup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:62355AFE-F239-49BB-8F33-C50CDCD900C8@.microsoft.com...
> Hmmmm
> 1. Are you saying that that the Master and MSDB will always stay in simple
> no matter what I pick?
> 2. If I do a full back up of the Master and MSDB every night and the
> recovery mode is locked to simple, the Transaction Log that comes with the
> full is not really used, it just seems to restore to the last "static" state.
> "Mike John" wrote:
>> Master and MSDB no choice as they get reset to simple anyway. Regular
>> (daily) full backups tends to be the most common.
>> Model - matters little but it does set the default recovey model for new
>> databses being created.
>> Mike John
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:70327943-6C4C-46B0-98AD-C875FCD7F9D1@.microsoft.com...
>> > What Recovery Model and what Backup Type should be used on the Master,
>> > Model
>> > and MSDB databases?
>>

No comments:

Post a Comment