The need to back up system databases?

Question

The need to back up system databases?

winston Smith

SSCoach

Points: 19500
More actions
October 15, 2010 at 4:48 am

#228068

I can see a use for backing up MSDB, as it stores all jobs. But in oure envrionment, we dont use encription, so no keys, we dont have any changes made to model and no extra sprocs added to master, besides what litespeed added.
To this end, is there any point in backing up master and model? I want to be sure im considering everything.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

I'm nearly the same winston; the only info that is in the master database that is special to me is logins, and I've created a script that pulls those out for disaster recovery/login recreation;

That said, i still take a master backup once every two weeks, along with model; there just might be a situation that my shortsighted-ness has not considered, and a backup might be a piece of that solution.

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!

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 2

For the sake of a few MB's of diskspace, is it really worth the risk not backing it up?

Hope this helps,
Rich

[p]
[/p]

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

Master contains all the database-related info, all logins, server-level permissions, linked servers, startup procedures.

If you don't have a backup, what will happen if the DB is corrupt or drive fails? Sure, you can recreate master, reattach all databases and run scripts, but a restore is probably faster.

That said, it doesn't need to be daily or even weekly If you make changes maybe twice a year, take backups of master maybe twice a year.

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

Geoff A SSChampion Points: 11511 More actions · Answer 4

i take a backup of master EVERYDAY because there is nothing better than seeing this at 7 am on a workday;

CHECKDB found 1 allocation errors and 2 consistency errors in database 'master'. 😉

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

RichardDouglas (10/15/2010)
For the sake of a few MB's of diskspace, is it really worth the risk not backing it up?

I'm with you on this one... at 3 mb a piece for the backup, I can find a spare GB to save 1 year worth of uncompressed backups.

That being said, maybe daily is pushing it if you're not changing security that often

daveb87 Ten Centuries Points: 1025 More actions · Answer 6

I think it is a good practice to backup the Master, MSDB and Model databases on a regular basis. Of the three, the Model is the least important unless you make changes to it. But I back it up anyway.

A small investment in time now could save hours later in the event of a disaster. Back them up!

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

daveb87 (10/15/2010)
I think it is a good practice to backup the Master, MSDB and Model databases on a regular basis.

It's vitally important!!

daveb87 (10/15/2010)
Of the three, the Model is the least important unless you make changes to it.

Au contrair mon pere!

This is extremely important. Imagine rebuiling your sql server databases using a different collation and restoring back your master and msdb which now use a different collation. Tempdb is built from the incorrect Model and that's where your problems will start!

For the size of them, always back up and restore them up as a set!

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 8

Let's also not forget backing up the distribution database, if using replication.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]