To create a database backupThe master database is backed up in the same way as user databases. If master is damaged in some way, for example because of media failure, 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, any changes since the last backup are lost if the backup is restored. For example, if a user database is created after master is backed up, tables and data added to the database, and then master is 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:
If a user database grows automatically, by virtue of the autogrow feature, to accommodate new data, this does not affect master. Adding and deleting files and filegroups does not affect master.
Database security operations, such as adding a user to a database, do not affect master.
Note Only full database backups of master can be created.
To create a database backup
