The following databases should be backed up regularly:
- The master database
- All production databases
Backing Up the master Database
The master database is the most important system database because it contains information about all of the databases in the system. Therefore, you should back up the master database on a regular basis. Additionally, you should back up the master database anytime certain statements and stored procedures are executed, because the Database Engine modifies the master database automatically.
Note – You can perform full database backups of the master database only. (The system does not support differential, transaction log, and file backups for the master database.)
activities cause the modification of the master database. Some of them are listed here:
- The creation, alteration, and removal of a database
- The alteration of the transaction log
Note – Without a backup of the master database, you must completely rebuild all system databases, because if the master database is damaged, all references to the existing user-defined databases are lost.
Backing Up Production Databases
You should back up each production database on a regular basis. Additionally, you should back up any production database when the following activities are executed:
- After creating it
- After creating indices
- After clearing the transaction log
- After performing nonlogged operations
Always make a full database backup after it has been created, in case a failure occurs between the creation of the database and the first regular database backup. Remember that backups of the transaction log cannot be applied without a full database backup.
Backing up the database after creation of one or more indices saves time during the restore process, because the index structures are backed up together with the data. Backing up the transaction log after creation of indices does not save time during the restore process at all, because the transaction log records only the fact that an index was created (and does not record the modified index structure).
Backing up the database after clearing the transaction log is necessary because the transaction log no longer contains a record of database activity, which is used to recover the database. All operations that are not recorded to the transaction log are called nonlogged operations. Therefore, all changes made by these operations cannot be restored during the recovery process.
