Backing Up the master Database

The master database must be backed up. If master is damaged in some way, for example because of media failure, an instance of Microsoft® SQL Server™ may not be able to start. In this event, it is necessary to rebuild master, and then restore the database from a backup.

Consider backing up master after any statement or system procedure is executed that changes information in master, for example, changing a server-wide configuration option. If master is not backed up after it changes and then the backup is restored, any changes since the last backup are lost. For example, a user database is created after master is backed up and tables and data are added to the database. If master is then restored because of a hard disk failure, the user database will not be known to SQL Server because there are no entries in the restored master database for this new user database. In this case, if all database files comprising the user database still exist on the disk(s), the user database can be created by attaching the database files. For more information, see Attaching and Detaching Databases.

Note  It is recommended that user objects not be created in master; otherwise master needs to be backed up more frequently. Additionally, user objects compete with the system objects for space.

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

• Creating or deleting a user database.

  If a user database grows automatically to accommodate new data, this does not affect master. Deleting files and filegroups does not affect master.

• Adding logins or other login security-related operations.

  Database security operations, such as adding a user to a database, do not affect master.

• Changing any server-wide or database configuration options.
  
  
• Creating or removing logical backup devices.
  
  
• Configuring the server for distributed queries and remote procedure calls, such as adding linked servers or remote logins.

Note  Only full database backups of master can be created.

To create a database backup