Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

What system databases do I need to backup? Expand / Collapse
Author
Message
Posted Thursday, June 21, 2012 11:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 1:54 PM
Points: 38, Visits: 167
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?
Post #1319524
Posted Thursday, June 21, 2012 12:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 12,905, Visits: 32,158
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1319528
Posted Thursday, June 21, 2012 12:09 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:09 PM
Points: 40,172, Visits: 36,560
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 2008, MVP
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

Post #1319530
Posted Thursday, June 21, 2012 12:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:46 PM
Points: 68, Visits: 422
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
Post #1319531
Posted Thursday, June 21, 2012 3:12 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, October 18, 2014 5:21 PM
Points: 5,877, Visits: 13,005
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.


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

Post #1319675
Posted Friday, June 22, 2012 7:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 1:54 PM
Points: 38, Visits: 167
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.
Post #1319930
Posted Sunday, June 24, 2012 8:25 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, October 18, 2014 5:21 PM
Points: 5,877, Visits: 13,005

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


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

Post #1320355
Posted Monday, June 25, 2012 11:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:02 PM
Points: 1,289, Visits: 2,842
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.



Post #1320810
Posted Monday, June 25, 2012 12:15 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:23 AM
Points: 3,108, Visits: 11,503
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.



Post #1320826
Posted Tuesday, June 26, 2012 1:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:13 PM
Points: 6,466, Visits: 13,919
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"
Post #1321055
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse