| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | |||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Back up and restore: System databases |
SQL Server maintains system databases essential for operation of a server instance. Several system databases must be backed up after every significant update. |
MashaMSFT |
mathoma |
randolphwest |
11/21/2023 |
sql |
backup-restore |
conceptual |
|
[!INCLUDE SQL Server]
[!INCLUDE ssNoVersion] maintains a set of system-level databases, called system databases, which are essential for the operation of a server instance. Several of the system databases must be backed up after every significant update. The system databases that you must always back up include msdb, master, and model. If any database uses replication on the server instance, you must also back up the distribution system database. Backups of these system databases let you restore and recover the [!INCLUDE ssNoVersion] system after a system failure, such as the loss of a storage device.
The following table summarizes all of the system databases.
| System database | Description | Backups required? | Recovery model | Comments |
|---|---|---|---|---|
| master | The database that records all of the system level information for a [!INCLUDE ssNoVersion] system. | Yes | Simple | Back up master as often as necessary to protect the data sufficiently for your business needs. We recommend a regular backup schedule, which you can supplement with an additional backup after a substantial update. Transaction log backups of master aren't supported. |
| model | The template for all databases that are created on the instance of [!INCLUDE ssNoVersion]. | Yes | User configurable1 | Back up model only when necessary for your business needs; for example, immediately after customizing its database options.Best practice: We recommend 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 database used by [!INCLUDE ssNoVersion] Agent for scheduling alerts and jobs, and for recording operators. msdb also contains history tables such as the backup and restore history tables. |
Yes | Simple (default) | Back up msdb whenever it's updated. |
| Resource Database (RDB) | A read-only database that contains copies of all system objects that ship with [!INCLUDE ssNoVersion] | No | None | The Resource database resides in the mssqlsystemresource.mdf file, which contains only code. Therefore, [!INCLUDE ssNoVersion] can't back up the Resource database.Note: You can perform a file-based or a disk-based backup on the mssqlsystemresource.mdf file by treating the file as if it were a binary (.exe) file, instead of a database file. But you can't use [!INCLUDE ssNoVersion] restore on the backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version. |
| tempdb | A workspace for holding temporary or intermediate result sets. This database is re-created every time an instance of [!INCLUDE ssNoVersion] is started. When the server instance is shut down, any data in tempdb is deleted permanently. |
No | Simple | You can't back up the tempdb system database. |
| Configure Distribution | A database that exists only if the server is configured as a replication Distributor. This database stores metadata and history data for all types of replication, and transactions for transactional replication. | Yes | Simple | For information about when to back up the distribution database, see Back Up and Restore Replicated Databases. |
1 To learn the current recovery model of the model, see View or change the recovery model of a database (SQL Server) or sys.databases (Transact-SQL).
System databases can be restored only from backups that are created on the version of [!INCLUDE ssNoVersion] that the server instance is currently running. For example, to restore a system database on a server instance that is running [!INCLUDE sssql16-md] with Service Pack 1, you must use a database backup that was created after the server instance was upgraded to [!INCLUDE sssql16-md] SP 1.
To restore any database, the instance of [!INCLUDE ssNoVersion] must be running. Startup of an instance of [!INCLUDE ssNoVersion] requires that the master database is accessible and at least partly usable. If master becomes unusable, you can return the database to a usable state in either of the following ways:
-
Restore
masterfrom a current database backup.If you can start the server instance, you should be able to restore
masterfrom a full database backup. For more information, see Restore the master database (Transact-SQL). -
Rebuild
mastercompletely.If severe damage to
masterprevents you from starting [!INCLUDEssNoVersion], you must rebuildmaster. For more information, see Rebuild system databases.[!IMPORTANT]
Rebuildingmasterrebuilds all of the system databases.
Under some circumstances, problems recovering the model database might require rebuilding the system databases or replacing the mdf and ldf files for the model database. For more information, see Rebuild system databases.