system database backup

Question

system database backup

SQL*

SSCertifiable

Points: 6638
More actions
June 15, 2009 at 5:28 am

#131121

hi,
when to take system database backups. best approach to take the system database backups.
any help topics please.
🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

msdb- whenever u add/delete/modify a job/maintanance plan, any thing that relates to sql server agent, you shud backup this database up.

model - this is the template for new databases, if you're making any change to this template, you should back it up. This also stores job histories, including backup/restore histories.

master - stores system wide configurations and server logins. backup whenever these changes take place.

Since sizes of these databases are relatively much smaller than user databases, weekly full backup looks fine in most of the cases.

Pradeep Singh

ps. One Orange Chip Points: 29422 More actions · Answer 2

msdb- whenever u add/delete/modify a job/maintanance plan, any thing that relates to sql server agent, you shud backup this database up.

model - this is the template for new databases, if you're making any change to this template, you should back it up. This also stores job histories, including backup/restore histories.

master - stores system wide configurations and server logins. backup whenever these changes take place.

Since sizes of these databases are relatively much smaller than user databases, weekly full backup looks fine in most of the cases.

Also take backup of resourcedatabase prior to applying any new patch...

Pradeep Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Personally I'd just back the system databases up once a week and leave it at that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

ps (6/15/2009)
model - this is the template for new databases, if you're making any change to this template, you should back it up. This also stores job histories, including backup/restore histories.

Model does not store job histories or backup/restore histories. Both of those are in MSDB.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ps. One Orange Chip Points: 29422 More actions · Answer 5

Thanks for pointing that out Gail. It was a typo error :w00t:

Pradeep Singh