system database backups missing

Posted on 2014-07-16
Last Modified: 2014-07-20
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.
Question by:pma111
    LVL 41

    Expert Comment

    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.
    LVL 22

    Accepted Solution

    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.
    LVL 3

    Author Comment

    Steve should it be a daily backup for those databases in your opinion as a dba
    LVL 22

    Expert Comment

    by:Steve Wales
    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.


    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now