The master database is backed up in the same way as user databases and should be dumped regularly and frequently. It is important to make backups of master after any statement or system procedure that records information in it is used.
For example, back up master after these statements and/or stored procedures are used:
| ALTER DATABASE | sp_addserver |
| CREATE DATABASE | sp_addumpdevice |
| DISK INIT | sp_configure |
| DISK MIRROR | sp_dropdevice |
| DISK UNMIRROR | sp_droplogin |
| DISK REMIRROR | sp_dropsegment |
| DISK RESIZE | sp_dropserver |
| various DBCC options such as SHRINKDB | sp_dropremotelogin |
| sp_addlogin | sp_extendsegment |
| sp_addremotelogin | sp_logdevice |
| sp_addsegment |
The master database backups are used as part of the recovery procedure when a failure that affects the master database occurs. If the master database is damaged, a special procedure must be used to recover it. For information, see Restoring the master Database, in this chapter.
Because master cannot be separated from its transaction log, you cannot separately back up the transaction log for master.
Note The master database contains the system catalog, which is the most important information about your SQL Server configuration. When dumping the master database, it is also advisable to make hard copies (using the SELECT * statement) of the sysdatabases, sysusages, and syslogins system tables. This information is useful in recovery procedures following DISK REINIT and DISK REFIT.