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
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28111 Visits: 39939
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 (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87069 Visits: 45267
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-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10298 Visits: 13687
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
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10298 Visits: 13687

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

---------------------------------------------------------------------
Markus
Markus
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2999 Visits: 3768
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
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5722 Visits: 11771
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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19928 Visits: 17242
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