Best Practices and Recommendations

Considerations for Backing Up and Restore of System Databases

Refer to the following tables for considerations for backup and restoration of system databases.

For Backup of System Databases

SQL server maintains a set of system level database which are essential for the operation of the server instance.

Several of the system databases must be backed up after every significant update, they include:

  1. master

  2. model

  3. msdb

  4. distribution (for SQL database with replication enabled only)

This table summarizes all of the system databases.

System Description Backup Suggestion
master The database that records all of the system level information of a SQL server system. Yes

To back up any database, the instance of SQL server must be running.

Startup of an instance of SQL server requires that the master database is accessible and at least partly usable.

Back up the master database as often as necessary to protect the data sufficiently for your business needs.

Microsoft recommends a regular backup schedule, which you can supplement with manual backup after any substantial update.
model The template for all databases that are created on the instance of SQL server. Yes

Backup the model database only when necessary, for example, after customizing its database options.

Microsoft recommends that you create only full database backups of model, as required. Because model is small and rarely changes, backing up the log is unnecessary.
msdb The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and for recording operators. It also contains history tables (e.g. backup/restore history table). Yes Back up the msdb whenever it is updated.
tempdb

A workspace for holding temporary or intermediate result sets.

This database is recreated every time an instance of SQL server is started.

 No The tempdb system database cannot be backed up.
distribution

The distribution database exists only if the server is configured as a replication distributor.

It stores metadata and history data for all types of replication, and transactions for transactional replication.

 Yes Replicated databases and their associated system databases should be backed up regularly.

For Restore of System Databases

System database Restoration suggestion
master

To restore any database, the instance of SQL server must be running. Startup of an instance of SQL server requires that the master database is accessible and at least partly usable.

Restore or rebuild the master database completely if master becomes unusable.
model

Restore the model database if:

  • The master database has been rebuilt

  • The model database has been damaged, for example due to media failure.

  • The model database has been modified, in this case, it is necessary to restore model from a backup when you rebuild master, because the Rebuild Master utility deletes and recreates model.
msdb Restore the msdb database if the master database has been rebuilt.
distribution

For restore strategies of distribution database, please refer to the following online document from Microsoft for more details:

Strategies for Backing Up and Restoring Snapshot and Transactional Replication

The following are some best practices and recommendations we strongly recommend you follow before you start any MS SQL Server backup and restore.

  1. For VSS backup mode, it is suggested to set the backup schedule to a time when system activity is low to achieve the best possible performance.

  2. It is recommended to use ODBC backup mode for backup of database with a high volume of transaction, since such setup may require frequent backups. Transaction log backup (which is only supported by ODBC backup mode) can be performed periodically and is less resource intensive than VSS based backup.

  3. For maximum data protection and restore options, it is recommended to configure:

    • At least one offsite or cloud destination

    • At least one local destination for fast recovery

  4. Perform test restores periodically to ensure your backup is set up and performed properly. Performing recovery test can also help identify potential issues or gaps in your recovery plan. It is important that you do not try to make the test easier, as the objective of a successful test is not to demonstrate that everything is flawless. There might be flaws identified in the plan throughout the test and it is important to identify those flaws.

  5. The Restore Raw File option is for advanced MS SQL Server administrator and should only be used if you have in-depth knowledge and understanding of your MS SQL Server, otherwise, it is not recommended to use this option as there are additional MS SQL techniques required to perform the manual restore.

  6. To ensure an optimal backup/restoration performance, it is highly recommended to set the temporary directory folder to a location with sufficient free disk space. It must be on another location other than Drive C: (e.g. Drive E:).

  7. The periodic backup schedule should be reviewed regularly to ensure that the interval is sufficient to handle the data volume on the machine. Over time, data usage pattern may change on a production server, i.e. the number of new files created, the number of files which are updated/deleted, and new users may be added etc.

    Consider the following key points to efficiently handle backup sets with periodic backup schedule.

    • Hardware - to achieve optimal performance, compatible hardware requirements is a must. Ensure you have the backup machine’s appropriate hardware specifications to accommodate frequency of backups,

      • so that the data is always backed up within the periodic backup interval

      • so that the backup frequency does not affect the performance of the production server

    • Network - make sure to have enough network bandwidth to accommodate the volume of data within the backup interval.

    • Retention Policy - also make sure to consider the retention policy settings and retention area storage management which can grow because of the changes in the backup data for each backup job.

Make sure that the latest version of AhsayOBM is installed directly on the MS SQL server as the backup of MS SQL server databases running on a remote machine is not supported.