[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 133
  • Last Modified:

system database backups missing

Please elaborate on the risks if you find the sql dba has not been backing up the default sql databases (ie msdb master model etc). When would you ever need to restore data from those databases.. what is the risk in not backing them up if any.
  • 2
1 Solution
If you don't store your own information to these system databases intentionally then you don't need their backup. Of course, if the SQL Server crashes then you have to make a fresh install and restore your production database only.

The backup copy of your production data is possible to restore on any SQL Server instance which you have available. The data version must be compatible to the SQL Server instance. The backward compatibility is good for MS SQL Server obviously.
Steve WalesSenior Database AdministratorCommented:
I respectfully disagree with pcelba.

You might be able to get by without model (which is really just the template used for creating a new database).

Master contains all the instance logins and system configuration data and also records the existence of all other databases in the instance.

Master contains all of the system level information for the instance.

If you have 100 databases in your instance, losing master is going to cause you no end of pain.

Msdb is used by SQL Server agent.  If you have dozens of Agent jobs, recreating all of them from scratch is also going to be a major pain.

If you don't have them backed up, make that happen immediately.
pma111Author Commented:
Steve should it be a daily backup for those databases in your opinion as a dba
Steve WalesSenior Database AdministratorCommented:
They shouldn't be that large ... I do mine daily.

The Doco says to back up as often as necessary to protect the data sufficiently for your business needs.

Refer http://msdn.microsoft.com/en-us/library/ms190190.aspx

Daily should be more than adequate.  If you find a day where you have a lot of core system level changes going on (lots of new users or databases or similar) then maybe an ad hoc backup after the changes are made just to be safe.

Remember, backups are an insurance policy.  You never need them until you NEED them.  I have been a homeowner for 25 years and have never made a claim on my homeowner's insurance.

However, I have never NOT been insured for the value of my house,

Treat your databases the same way.  Data loss can ruin a company.

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now