SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What system databases do I need to backup?


What system databases do I need to backup?

Author
Message
D. Clark
D. Clark
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 173
I am convinced that I need to backup my master and msdb databases, but what other databases should I backup as well?

After doing a little bit of research I have come to the assumption that I don't need to backup my model database. My reasoning is that I don't believe we ever modified it, and therefore it is vanilla. This then leads me to believe that if I were to lose/restore a database server that the newly installed model database would match the old one. In fact, MS says that you don't need to backup this database if you have never updated it.Is my reasoning sound? Furthermore, is there a way to check if it was modified in case I am incorrect? Am I missing anything?

Then there are tempdb and mssqlsystemresource databases. As for tempdb, I am pretty sure that is just space for operations and therefore a backup is not needed, but what about mssqlsystemresource? Does this contain anything worth holding onto? After all wouldn't MS want us to see it if it was worth backing up?
Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75179 Visits: 40985
something to keep in mind: system databases cannot be restored from previous versions or servicepacks;(regular databases are no problem)

so say you backed up master, msdb and model in RTM (release to market base version), then upgraded to SP2;

if any of those db's get corrupted, you cannot restore from those previous backups; after an upgrade you need to do a fresh set of backups that match the exact version.

so if model got corrupted, you'd have to get a model from another instance with the same service packs applied (and maybe CU's as well...i'd have to double check on that)

also on bootup, isn't tempdb born as a copy of model?
it might be a pain to track something like that down (especially if you are in an environment with out too many servers!)

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233507 Visits: 46361
Back model up (and also I often take copies of its data and log files). Otherwise recovering from a corrupt model is an absolute pain.

It doesn't need to be backed up regularly if you never change it, just have a valid backup as of the last time a service pack/hotfix/cumulative update was installed.

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


UconnDBA
UconnDBA
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 444
In every instance I create a maintenance plan that backs up ALL databases. This includes system and user databases. This way if/when user databases are created they will automatically backed up everynight. This backs up master, model and msdb. They are generally very small so I dont really worry about whether or not to back up. mssqlsystemresource is located on installation drive, usually c:. These can be backed up with OS disk backup.

John
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25636 Visits: 13701
backup master, msdb and model. Tempdb and resource database cannot be backed up. Tempdb is recreated on server restart so no point even if you could back it up.

Take a flat file copy of the resource database files on every upgrade, they are files on disk so can get corrupted. It certainly does contain data worth holding onto, it contains all the system metadata and SQL will not operate without it.

I also take flat file copies of the master, msdb and model database files on each upgrade, makes recovering from the loss of those databases so much easier.

---------------------------------------------------------------------
D. Clark
D. Clark
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 173
Thanks all, I will backup the model as well since it seems like it won't hurt. I will handle that backup manually since we are not modifying it. If I ever find the urge to modify it I will take another backup.

george sibbald (6/21/2012)
Take a flat file copy of the resource database files on every upgrade, they are files on disk so can get corrupted. It certainly does contain data worth holding onto, it contains all the system metadata and SQL will not operate without it.


I assume you are just copying the mssqlsystemresource files and that's it? Then if and when you need to restore you just copy it back in.
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25636 Visits: 13701

I assume you are just copying the mssqlsystemresource files and that's it? Then if and when you need to restore you just copy it back in.


correct

---------------------------------------------------------------------
Summer90
Summer90
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7443 Visits: 3831
The other point is that it is so small it is easier to do daily backups with master and msdb anyway. Then you know you have current backups.

The other point to note, that a previous poster stated is very valid. Any service packs or CU updates to SQL Server you will need a backup of anyway because you cannot restore an older version of it if you need to.
Michael Valentine Jones
Michael Valentine Jones
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15113 Visits: 11848
The model database is usually so small that you should just back it up at the same time as the other system databases. My preference is to ceate a maintenance plan to do a full backup of the system databases every day, and keep them for a week

Better to have a backup you don't need than to need one you don't have.
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56439 Visits: 17729
Lowell (6/21/2012)
you'd have to get a model from another instance with the same service packs applied (and maybe CU's as well...i'd have to double check on that)

Same collation too ;-)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search