Default Recovery Model

Hello Group,
why does the default install of SQL Server 2000 place the master db in the
simple recover model? should I switch to the full model and then I can use
trans logs for point in time recover. What is the normal setting for the
recovery of the master db?
Rich,
User-defined objects should not be created in the Master system database.
The recovery model is SIMPLE and cannot be changed.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
> Hello Group,
> why does the default install of SQL Server 2000 place the master db in the
> simple recover model? should I switch to the full model and then I can
> use
> trans logs for point in time recover. What is the normal setting for the
> recovery of the master db?
|||Probably because the master isn't written to nearly as frequently as user
databases or even msdb. Of course, I'm just guessing.
I leave it at simple, as my master get backed up daily (nightly?)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
> Hello Group,
> why does the default install of SQL Server 2000 place the master db in the
> simple recover model? should I switch to the full model and then I can
> use
> trans logs for point in time recover. What is the normal setting for the
> recovery of the master db?
|||Or rather...should not be changed.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eqnZDW1yFHA.460@.TK2MSFTNGP15.phx.gbl...
> Rich,
> User-defined objects should not be created in the Master system database.
> The recovery model is SIMPLE and cannot be changed.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
>
|||Rich,
Kevin brings up another key topic - be sure to backup the Master and MSDB
database anytime changes occur that affect them (daily would be suggested).
HTH
Jerry
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:uQavLW1yFHA.1264@.tk2msftngp13.phx.gbl...
> Probably because the master isn't written to nearly as frequently as user
> databases or even msdb. Of course, I'm just guessing.
> I leave it at simple, as my master get backed up daily (nightly?)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:6B7DDC52-F7D6-43EF-8F5D-AB60957DBF29@.microsoft.com...
>
|||Hello Jerry,
I do a full backup of Master nightly. I was adding a seporate job to backup
only the trans log to try to do a point in time recover. Is the master
locked into simple?
"Jerry Spivey" wrote:

> Rich,
> Kevin brings up another key topic - be sure to backup the Master and MSDB
> database anytime changes occur that affect them (daily would be suggested).
> HTH
> Jerry
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:uQavLW1yFHA.1264@.tk2msftngp13.phx.gbl...
>
>
|||Rich,
You can change it to FULL but you still not be able to perform transaction
log backups so what's the point. Here is the error you will get when you
try a transaction log backup of the Master system database:
Server: Msg 4212, Level 16, State 1, Line 1
Cannot back up the log of the master database. Use BACKUP DATABASE instead.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:F957C681-26E6-4FC6-B2CA-8338D1D70B13@.microsoft.com...[vbcol=seagreen]
> Hello Jerry,
> I do a full backup of Master nightly. I was adding a seporate job to
> backup
> only the trans log to try to do a point in time recover. Is the master
> locked into simple?
> "Jerry Spivey" wrote:
|||Hello Jerry,
you are correct, it appears to be locked by Microsoft. I just wanted to be
sure you could not do it. I will remove Master from the trans log backups.
Now what about the MSDB, is it "locked" in the same manner?
Rich
"Jerry Spivey" wrote:

> Rich,
> You can change it to FULL but you still not be able to perform transaction
> log backups so what's the point. Here is the error you will get when you
> try a transaction log backup of the Master system database:
> Server: Msg 4212, Level 16, State 1, Line 1
> Cannot back up the log of the master database. Use BACKUP DATABASE instead.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP LOG is terminating abnormally.
>
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:F957C681-26E6-4FC6-B2CA-8338D1D70B13@.microsoft.com...
>
>
|||Rich,
No.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:1AD22F41-6494-41F5-8852-692714EB366C@.microsoft.com...[vbcol=seagreen]
> Hello Jerry,
> you are correct, it appears to be locked by Microsoft. I just wanted to
> be
> sure you could not do it. I will remove Master from the trans log
> backups.
> Now what about the MSDB, is it "locked" in the same manner?
> Rich
> "Jerry Spivey" wrote:
|||Hi,
No, For MSDB you can set to FULL recovery model and do the transaction log
backup.
But my recommendation is:-
1. Master database :- Do a full database backup every day night
2. MSDB:- Do full database backup every 4 hours (Since it stores backup and
restore informations)
3. user databases:- Set FULL recovery model and do Transaction log backup
every 30 minutes
Thanks
Hari
SQL Server MVP
Thanks
Hari
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:1AD22F41-6494-41F5-8852-692714EB366C@.microsoft.com...[vbcol=seagreen]
> Hello Jerry,
> you are correct, it appears to be locked by Microsoft. I just wanted to
> be
> sure you could not do it. I will remove Master from the trans log
> backups.
> Now what about the MSDB, is it "locked" in the same manner?
> Rich
> "Jerry Spivey" wrote: