Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    London
    Posts
    169

    Unanswered: backup system databases

    hi,

    I have just started a new job and they have nightly backups of the master, msdb and model databases.

    But why would you need a nightly backup of the model database? surely the model database never changes, so would a one time backup not be sufficient?

    Please advise me if I am incorrect

    Cheers

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Usually yes, sometimes no.

    Every new database created on the server is a copy of Model. So it you have common tables, stored procs, functions, roles, or logins that you want included in every new database, those can be put in the Model database.

    And that would justify backing it up.

    Frankly, I back up EVERY database. Why not? It makes admin easier because you don't have to deal with exceptions, and I can't think of a time I've ever whacked myself on the head and thought "Damn! I wish I hadn't backed up that data!"
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I would never risk having inconsistent databases, ie master from last night, model from last week.
    A model backup does not take up any resources at all. If it were a 20GB database, that of course might change things.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by blindman
    ...Frankly, I back up EVERY database. Why not? It makes admin easier because you don't have to deal with exceptions...
    Including TEMPDB?

    In addition to backing up system databases it is also advisable to create re-runnable scripts that would restore not only logins without having to restore master, but also server-wide permissions. Not every disaster recovery calls for a full master restore, especially if one can be avoided.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, I don't back up TEMPDB.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •