Considerations for Backing Up the model and msdb Databases

Článek
12/03/2008

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 the full recovery model.

To create a full database backup

To schedule backup jobs

Maintenance Plan Wizard

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 SQL Server 2005 Integration Services (SSIS) 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.
  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.

Change History

Release	History
5 December 2005	New content: Added note documenting when the recovery model of msdb is reset to SIMPLE.

Sdílet prostřednictvím

Considerations for Backing Up the model and msdb Databases

Overview of the model and msdb Databases

When to Back Up the model and msdb Databases

See Also

Tasks

Concepts

Help and Information

Change History

Další materiály