Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Backups » What system databases do I need to backup?
Add to briefcase
21 posts, Page 1 of 3
123»»»

What system databases do I need to backup? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
D. Clark
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: Wednesday, May 15, 2013 9:29 AM
Points: 29, Visits: 119
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
Lowell
Posted Thursday, June 21, 2012 12:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 11,640, Visits: 27,725
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
GilaMonster
Posted Thursday, June 21, 2012 12:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 37,729, Visits: 29,990
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
UconnDBA
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: Today @ 11:15 AM
Points: 53, Visits: 313
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
george sibbald
Posted Thursday, June 21, 2012 3:12 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:22 AM
Points: 5,269, Visits: 11,207
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
D. Clark
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: Wednesday, May 15, 2013 9:29 AM
Points: 29, Visits: 119
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
george sibbald
Posted Sunday, June 24, 2012 8:25 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:22 AM
Points: 5,269, Visits: 11,207

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
Markus
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: Today @ 5:14 AM
Points: 1,048, Visits: 2,216
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
Michael Valentine Jones
Posted Monday, June 25, 2012 12:15 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 2,945, Visits: 10,511
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
Perry Whittle
Posted Tuesday, June 26, 2012 1:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:53 AM
Points: 5,204, Visits: 11,158
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
21 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.