December 13, 2002 at 9:51 am
I can't perform master database log backup in two SQL2k servers with SP2 latest patches and Win2K standard SP3. In mainteinance plan, master log backup got error message 'Backup can not perform on this database. This subtask is ignored'. Actually from the database option, I already set recovery model to 'full' on master, model and msdb. With script backup Log to devices, got error 'Server: Msg 4212,Level 16, State 1, Line 1 Cannot backup the log of the master database. Use BACKUP DATABASE instead. Server: Msg 3013, Level 16, State1, Line1 BACKUP LOG is terminating abnormally.' Does any DBA encountered this? Thank you for your advice!
-Angila
December 13, 2002 at 11:04 am
My understanding (and experience) is that MASTER is set for SIMPLE recovery method. You can ONLY do a FULL BACKUP of MASTER. LOG backups are not an option and I don't think differential backups are an option either. And as far as I have been able to figure out, you can't change the recovery type for the MASTER database.
-Bill
December 13, 2002 at 11:47 am
Hi Bill,
I did make the recovery model change from default 'SIMPLE' to 'FULL' and after this changing LOG backup worked through in msdn and model but not master database. That's the mystery I can't understand. Thanks the reply!
-Angila
December 13, 2002 at 12:25 pm
I found more in Books OnLine....it says 'Only a full database backup can be perfomed on the master database.'
Ref: Books OnLine, Index tab, keywords BACKUP DATABASE. Select the option that deals with Transact SQL, then scroll down until you get to the section "Arguments".
-Bill
December 13, 2002 at 12:43 pm
Thanks, I got it. SQK2k with less flexibility on this?
-Angila
December 17, 2002 at 2:49 am
So the recommendation is to leave the master database set to simple recovery model and only doing full backups on this? But for msdb, also set to simple, would it not be advisable to change to full recovery model and the doing transaction log backups?
Brgds
Jonas
BrgdsJonas
December 17, 2002 at 4:42 am
Yes all changes to master are considered critical and you cannot leave the DB offline to recover additional backups to master so Full Backup is all that is supported with master.
December 17, 2002 at 7:06 am
I only do the full backup for Master, Model, and MSDB. I do the transaction log backups, differential backups, and full backups for my database.
Why? Well as I understand it, the three M's (Master, Model, and MSDB) don't normally change all that often. Master only changes when you add users or the database schema changes. Model never changes unless you manually make a change to it. MSDB only changes when you create new jobs. So, since I don't do any of that often, I just do a full backup of the three M's once a week. For me that's probably too often, most likely I could get away with doing those once a month and not lose anything.
-SQLBill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply