June 27, 2003 at 4:11 am
Recently every morning MSDB has been set to SIMPLE recovery model even though I have set it to FULL the previous day. What can be causing this and how do I find out?
Cheers.
June 27, 2003 at 6:45 am
The reason for this is probably that you stop your server at night. SQL2K sets the recovery model for the msdb to simple at startup.
The way I solved it, create a job, scheduled at SQL Agent startup,which changes the recovery model and makes a full backup afterwards.
[font="Verdana"]Markus Bohse[/font]
June 27, 2003 at 6:49 am
Cheers. Just out of interest - why does it set this as default - is there a good reason? (I want to perform trans backups)
June 27, 2003 at 7:02 am
quote:
why does it set this as default - is there a good reason?
No idea, I always wondered about it myself if anyone knows a good reason.
[font="Verdana"]Markus Bohse[/font]
June 27, 2003 at 1:45 pm
By default msdb set to simple recovery model, since we don’t take the log backups for msdb, we don’t need a full recovery model. Simple database recovery is nothing but checking the Truncate log on checkpoint is on. Which is why MS recommends taking
full backup after new jobs creation or modification.
Shas3
June 27, 2003 at 9:07 pm
I've never seen a good reason either. I always force the recovery mode after each restart. Why give up the benefit of being able to do a point in time restore if something happens? You could do a full backup after each change to a job or package, but if you have very many, it gets bulky.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply