Udostępnij za pośrednictwem

Considerations for Backing Up the master Database

  • Artykuł

You must back up the master database. The master database records all of the system-level information for a Microsoft SQL Server system, such as login accounts, system configuration settings, endpoints and credentials, and the information required to access the other databases. The master database also records the initialization information that is required for a server instance to start. For more information, see master Database.

We recommend that you schedule routine full database backups of master frequently enough to protect your data sufficiently to meet for your business needs. If master is damaged while a server instance is running, you can fix the damaged database easily by restoring a recent full database backup of master if one has been created.

If a server instance cannot start because master is damaged, master must be rebuilt. Rebuilding master reverts all of the system databases to their original state. For this reason, rebuilding master entails additional steps. For more information, see Considerations for Rebuilding the master Database.

After any statement or system procedure is executed that changes information in master, it is advisable to back up master; for example, after changing a server-wide configuration option. If master is not backed up after it changes and the backup is restored, any changes since the last backup are lost.

We recommend that user objects not be created in master. If you do create user objects in master, however, your backup schedule should be frequent enough to protect that user data.

Example

After master is backed up, a user database is created and tables and data are added to the database. If master is then restored because of a hard disk failure, the user database is not known to SQL Server, because no entries exist for it in the restored master database. At this point, if all database files still exist on the disk(s), you can recreate the user database by attaching its database files. For more information, see Detaching and Attaching Databases.

Reasons to Back Up master

The types of operations that cause master to be updated, and that require a backup to take place, include the following:

  • Creating or deleting a user database.
    If a user database grows automatically to accommodate new data, master is not affected.
  • Adding or removing files and filegroups.
  • Adding logins or other operations that are related to login security.
    Database security operations, such as adding a user to a database, do not affect master.
  • Changing server-wide or database configuration options.
  • Creating or removing logical backup devices.
  • Configuring the server for distributed queries and remote procedure calls (RPCs), such as adding linked servers or remote logins.

Note

Only full database backups of master can be created.

To create a database backup

See Also

Concepts

Considerations for Restoring the master Database

Help and Information

Getting SQL Server 2005 Assistance