Share via

  • Article

Considerations for Backing Up the model and msdb Databases

It is important to back up model or msdb after any operation that updates the database. Backups are essential to allow you to restore a database if it is damaged, for example because of media failure:

  • If msdb is damaged, any scheduling information that is used by SQL Server Agent is lost and must be re-created manually. Backup and restore history information is also lost.

  • If model is damaged, and no current backup is available, any user-specific template information that has been added to model is lost and must be re-created manually.

You back up the model and msdb databases in the same way as user databases. Back up model and msdb regularly if they are changed.

Overview of the model and msdb Databases

The model and msdb databases perform the following functions:

  • The msdb database is used by SQL Server, SQL Server Management Studio, and SQL Server Agent to store data, including scheduling information and backup and restore history information.

    SQL Server automatically maintains a complete online backup-and-restore history in msdb. This information includes the name of the party that performed the backup, the time of the backup, and the devices or files where the backup is stored. SQL Server Management Studio uses this information to propose a plan for restoring a database and applying any transaction log backups. Backup events for all databases are recorded even if they were created with custom applications or third-party tools. For example, if you use a Microsoft Visual Basic application that calls SQL Server Management Objects (SMO) objects to perform backup operations, the event is logged in the msdb system tables, the Microsoft Windows application log, and the SQL Server error log.

    By default, msdb uses the simple recovery model. If you use the backup and restore history information in msdb when you recover user databases, we recommend that you use the full recovery model for msdb and that you consider placing the msdb transaction log on fault tolerant storage.

    Important

    When SQL Server is installed or upgraded and whenever Setup.exe is used to rebuild the system databases, the recovery model of msdb is automatically set to SIMPLE.

  • The model database is the template used by Microsoft SQL Server when you create a user database. The entire contents of the model database, including database options, are copied to the new database. Some of the settings of model are also used for creating a new tempdb during start up, so the model database must always exist on a SQL Server system.

    Newly created user databases use the same recovery model as the model database. The default is user configurable. To learn the current recovery model of the model, see How to: View or Change the Recovery Model of a Database (SQL Server Management Studio).

To create a full database backup

To schedule backup jobs

When to Back Up the model and msdb Databases

It is important to back up model or msdb after any operation that modifies the database. These databases are modified in the following ways:

  • The msdb database is updated automatically by the following actions:

    • Scheduling tasks.

    • Storing Integration Services packages that are created with the Import/Export Wizard in an instance of SQL Server.

    • Maintaining online backup and restore history.

    • Replication.

    • Setup.exe resets the recovery model to SIMPLE.

    • Additions or changes to Policy-Based Management policies or conditions.

    We recommend that you do not create user objects in msdb. If you choose to create user objects in msdb, its backup schedule should be frequent enough to protect changes to user objects.

  • The model database is updated only by user changes.