系統資料庫的備份與還原 (SQL Server)Back Up and Restore of System Databases (SQL Server)

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL ServerSQL Server 會維護一組系統層級資料庫,即「系統資料庫」 ,這對伺服器執行個體的運作而言是不可或缺的。maintains a set of system-level databases, 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. 您一定要備份的系統資料庫包括 msdbmastermodelThe system databases that you must always back up include msdb, master, and model. 如果有任何資料庫在伺服器執行個體上使用複寫,您還必須備份 distribution 系統資料庫。If any database uses replication on the server instance, there is a distribution system database that you must also back up. 這些系統資料庫的備份可讓您在發生系統失敗 (如硬碟故障) 時還原和復原 SQL ServerSQL Server 系統。Backups of these system databases let you restore and recover the SQL ServerSQL Server system in the event of system failure, such as the loss of a hard disk.

下表摘要列出所有系統資料庫。The following table summarizes all of the system databases.

系統資料庫System database DescriptionDescription 需要備份嗎?Are backups required? 復原模式Recovery model 註解Comments
mastermaster 記錄 SQL ServerSQL Server 系統之所有系統層級資訊的資料庫。The database that records all of the system level information for a SQL ServerSQL Server system. Yes SimpleSimple 請視需要經常備份 master ,充分地保護資料以滿足您業務的需求。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.
modelmodel SQL ServerSQL Server執行個體上建立之所有資料庫的範本。The template for all databases that are created on the instance of SQL ServerSQL Server. Yes 可由使用者設定*User configurable* 只在有業務上有需要時才備份 model ;例如,在自訂資料庫選項之後立即備份。Back up model only when necessary for your business needs; for example, immediately after customizing its database options.

最佳做法: 我們建議您在必要時僅建立 model 的完整資料庫備份。Best practice: We recommend that you create only full database backups of model, as required. 因為 model 很小,而且少有變更,所以不需要備份記錄。Because model is small and rarely changes, backing up the log is unnecessary.
msdbmsdb SQL ServerSQL Server Agent 用於排程警示和作業以及用於記錄操作員的資料庫。The database used by SQL ServerSQL Server Agent for scheduling alerts and jobs, and for recording operators. msdb 也包含歷程記錄資料表,例如備份和還原歷程記錄資料表。msdb also contains history tables such as the backup and restore history tables. Yes 簡單 (預設值)Simple (default) 每當 msdb 更新時就加以備份。Back up msdb whenever it is updated.
Resource (RDB)Resource (RDB) 唯讀資料庫,其中包含下者隨附之所有系統物件的複本: SQL ServerSQL ServerA read-only database that contains copies of all system objects that ship with SQL ServerSQL Server No - Resource 資料庫位於 mssqlsystemresource.mdf 檔案中,這個檔案中只包含程式碼。The Resource database resides in the mssqlsystemresource.mdf file, which contains only code. 因此, SQL ServerSQL Server 無法備份 Resource 資料庫。Therefore, SQL ServerSQL Server cannot back up the Resource database.

注意:您可以將 mssqlsystemresource.mdf 檔視為二進位 (.exe) 檔案而非資料庫檔案,藉以針對該檔案執行以檔案或磁碟為基礎的備份。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. 但是您無法在這些備份上使用 SQL ServerSQL Server 還原。But you cannot use SQL ServerSQL Server restore on the backups. 還原 mssqlsystemresource.mdf 的備份副本只能手動完成,而且您必須小心不要使用過期或可能不安全的 Resource 資料庫來覆寫目前的資料庫。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.
tempdbtempdb 用以保存暫存或中繼結果集的工作空間。A workspace for holding temporary or intermediate result sets. 每當 SQL ServerSQL Server 執行個體啟動時,就會重新建立此資料庫。This database is re-created every time an instance of SQL ServerSQL Server is started. 當伺服器執行個體關閉時, tempdb 中的任何資料都會被永久刪除。When the server instance is shut down, any data in tempdb is deleted permanently. No SimpleSimple 您不能備份 tempdb 系統資料庫。You cannot 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 SimpleSimple 如需有關何時備份 distribution 資料庫的詳細資訊,請參閱備份及還原複寫的資料庫For information about when to back up the distribution database, see Back Up and Restore Replicated Databases.

*若要了解模型的目前復原模式,請參閱檢視或變更資料庫的復原模式 (SQL Server)sys.databases (Transact-SQL)*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).

還原系統資料庫的限制Limitations on Restoring System Databases

  • 您只能從伺服器執行個體目前執行之 SQL ServerSQL Server 版本所建立的備份還原系統資料庫。System databases can be restored only from backups that are created on the version of SQL ServerSQL Server that the server instance is currently running. 例如,若要還原執行於 SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 之伺服器執行個體上的系統資料庫,您必須使用在伺服器執行個體升級至 SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1 之後所建立的資料庫備份。For example, to restore a system database on a server instance that is running on SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1, you must use a database backup that was created after the server instance was upgraded to SQL Server 2012 (11.x)SQL Server 2012 (11.x) SP1.

  • 若要還原任何資料庫, SQL ServerSQL Server 的執行個體必須在執行中。To restore any database, the instance of SQL ServerSQL Server must be running. SQL ServerSQL Server master 資料庫必須是可存取的,而且至少部分可用, 的執行個體才能夠啟動。Startup of an instance of SQL ServerSQL Server requires that the master database is accessible and at least partly usable. 如果 master 資料庫變得無法使用,您可以使用下列任一方法將資料庫回復到可用狀態:If master becomes unusable, you can return the database to a usable state in either of the following ways:

    • 從現行資料庫備份來還原 masterRestore master from a current database backup.

      如果您可以啟動伺服器執行個體,應該就能夠從完整資料庫備份來還原 masterIf you can start the server instance, you should be able to restore master from a full database backup.

    • 完全重建 masterRebuild master completely.

      如果 master 嚴重損壞,使您無法啟動 SQL ServerSQL Server,則必須重建 masterIf severe damage to master prevents you from starting SQL ServerSQL Server, you must rebuild master. 如需詳細資訊,請參閱 重建系統資料庫For more information, see Rebuild System Databases.

      重要

      重建 master 將會重建所有的系統資料庫。Rebuilding master rebuilds all of the system databases.

  • 在某些情況下,復原 model 資料庫若發生問題,可能就需要重建系統資料庫,或是置換 model 資料庫的 mdf 和 ldf 檔案。Under some circumstances, problems recovering the model database may require rebuilding the system databases or replacing the mdf and ldf files for the model database. 如需詳細資訊,請參閱 重建系統資料庫For more information, see Rebuild System Databases.

相關工作Related Tasks

另請參閱See Also

散發資料庫 Distribution Database
master 資料庫 master Database
msdb 資料庫 msdb Database
Model 資料庫 model Database
Resource 資料庫 Resource Database
tempdb 資料庫tempdb Database