Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2007-02-20 : 14:39:07
|
| 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 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-20 : 15:07:47
|
| You can't backup the transaction log of system databases.Tara Kizer |
 |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-02-20 : 15:37:30
|
| Tara,You are absolutely correct - I don't know what I was thinking - we had this discussion previously about the databases in a simple recovery model.However, the 'model' system db follows the full recovery model. Wouldn't I be able to back up its transaction log file (assuming it would be necessary for some reason)?Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-20 : 15:43:56
|
| I can't imagine why you'd have so much changes in the model database on a daily basis that would require ever performing a point in time restore on it. I always exclude model from my list of databases that I will be performing tlog backups on.To determine what databases require transaction log backups, you need to answer the question of whether you'd need to recover data at a point in time rather than just from the full backup. For the model database, I seriously doubt anyone would ever restore to a point in time.Tara Kizer |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-02-20 : 16:16:08
|
| Got it.Thanks a lot! |
|
|
|
|
|
|
|
|
Backing up trans log of system dbs