February 20, 2007 at 12:41 pm
Guys,
Should the transaction log of the system databases be backed up and if so, how often should this be done, generally?
Currently, we are backing up the system databases daily. The transaction log of the system databases, however, is not backed up.
Recently, I was wondering what exactly could happen, which would imply that we need to back up the transaction log of the system databases.
As far as I understand, 'msdb' contains things like jobs, dts. As such, if no jobs/dts are added in the middle of the day, there is no need to back up the trans log of this db - there could even be no reason, in such case, to back this db up at all!
'master' db would change if we add/modify objects/properties and so on ... so unless many changes are made, no need for backing up its trans log.
Having said that, could anyone enlighten me as to whether it is necessary to back up the transaction log of the system dbs and/or what it depends on and/or when it is suggested to do so?
Thanks you
February 20, 2007 at 1:32 pm
On a personal note I do not.The once a night backups of the system DBs seem more than enough in my experience
February 20, 2007 at 1:41 pm
Thanks ... although I just learned/realized that since msdb and master system dbs follow the simple recovery model, I cannot back-up their transaction log file even if I wanted to (unless I change their recovery model to full, of course)
February 20, 2007 at 3:43 pm
And you can not change master to Full. Believe me on this. It will let you do it, it will appear to be done, but then it will switch back on its own and tlog backups will fail.
-SQLBill
February 21, 2007 at 12:46 am
Transaction log backups of master database are not allowed
http://support.microsoft.com/kb/285288
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
MohammedU
Microsoft SQL Server MVP
February 28, 2007 at 12:32 pm
Thanks for the info guys!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply