檢視或變更資料庫的復原模式 (SQL Server)View or Change the Recovery Model of a Database (SQL Server)

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

此主題描述如何使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL檢視或變更資料庫。This topic describes how to view or change the database by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

「復原模式」 是一項資料庫屬性,可控制交易的記錄方式、是否需要 (及允許) 備份交易記錄,以及可用的還原作業類型。A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. 復原模式共有三種:簡單、完整和大量記錄。Three recovery models exist: simple, full, and bulk-logged. 一般而言,資料庫會使用完整復原模式或簡單復原模式。Typically, a database uses the full recovery model or simple recovery model. 資料庫可以隨時切換到另一個復原模式。A database can be switched to another recovery model at any time. model 資料庫會設定新資料庫的預設復原模式。The model database sets the default recovery model of new databases.

如需更深入的解釋,請參閱復原模式For a more in depth explanation, see recovery models.

開始之前Before you begin

  • 完整復原模式或大量記錄復原模式 before switching from the full recovery or bulk-logged recovery model.Back up the transaction log before switching from the full recovery or bulk-logged recovery model.

  • 在大量記錄模式下無法使用時間點復原。Point-in-time recovery is not possible with bulk-logged model. 在需要交易記錄還原的大量記錄復原模式下執行交易,可能會有資料遺失的風險。Running transactions under the bulk-logged recovery model that require a transaction log restore can exposed them to data loss. 若要在災害復原的情況下獲得最佳資料復原能力,請只在下列情況下切換到大量記錄復原模式:To maximize data recoverability in a disaster-recovery scenario, switch to the bulk-logged recovery model only under the following conditions:

    • 資料庫中目前不允許有使用者。Users are currently not allowed in the database.

    • 大量處理期間進行的所有修改都可復原,不必依賴建立記錄備份;例如,重新執行大量處理序。All modifications made during bulk processing are recoverable without depending on taking a log backup; for example, by re-running the bulk processes.

    如果您滿足這兩項條件,還原大量記錄復原模式下備份的交易記錄時,就不必擔心資料遺失。If you satisfy these two conditions, you will not be exposed to any data loss while restoring a transaction log that was backed up under the bulk-logged recovery model..

注意!Note! 如果您在大量作業期間切換到完整復原模式,大量作業記錄將從最小記錄變成完整記錄,反之亦然。If you switch to the full recovery model during a bulk operation, bulk operations logging changes from minimal logging to full logging, and vice versa.

必要權限Required permissions

需要資料庫的 ALTER 權限。Requires ALTER permission on the database.

使用 SQL Server Management StudioUsing SQL Server Management Studio

檢視或變更復原模式To view or change the recovery model

  1. 連接到適當的 SQL Server Database EngineSQL Server Database Engine執行個體之後,請在 [物件總管] 中按一下伺服器名稱以展開伺服器樹狀目錄。After connecting to the appropriate instance of the SQL Server Database EngineSQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

  2. 展開 [資料庫] ,然後視資料庫而定,選取使用者資料庫,或者展開 [系統資料庫] 並選取一個系統資料庫。Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

  3. 以滑鼠右鍵按一下資料庫,然後按一下 [屬性] ,這會開啟 [資料庫屬性] 對話方塊。Right-click the database, and then click Properties, which opens the Database Properties dialog box.

  4. [選取頁面] 窗格中,按一下 [選項]In the Select a page pane, click Options.

  5. 目前的復原模式會顯示在 [復原模式] 清單方塊中。The current recovery model is displayed in the Recovery model list box.

  6. 或者,您可以從不同的模式清單中選取來變更復原模式。Optionally, to change the recovery model select a different model list. 這些選擇包括 [完整] 、[大量記錄] 或 [簡單] 。The choices are Full, Bulk-logged, or Simple.

  7. 按一下 [確定] 。Click OK.

我們會持續聽取您的意見: 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例),請告訴我們。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 您可以按一下此頁面底部 [意見反應] 區段中的 [本頁] 按鈕。You can click the This page button in the Feedback section at the bottom of this page. 我們通常會在隔天閱讀有關 SQL 的每一個意見反應。We read every item of feedback about SQL, typically the next day. 謝謝。Thanks.

使用 Transact-SQLUsing Transact-SQL

檢視復原模式To view the recovery model

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase Engine.

  2. 在標準列中,按一下 [新增查詢]From the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 這個範例示範如何查詢 sys.databases 目錄檢視,以了解 model 資料庫的復原模式。This example shows how to query the sys.databases catalog view to learn the recovery model of the model database.

SELECT name, recovery_model_desc  
   FROM sys.databases  
      WHERE name = 'model' ;  
GO  
  

變更復原模式To change the recovery model

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase Engine.

  2. 在標準列中,按一下 [新增查詢]From the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 這個範例示範如何使用 model ALTER DATABASE FULL 陳述式的 SET RECOVERY 選項,將 資料庫的復原模式變更為This example shows how to change the recovery model in the model database to FULL by using the SET RECOVERY option of the ALTER DATABASE statement.

USE [master] ;  
ALTER DATABASE [model] SET RECOVERY FULL ;  

建議:在您變更復原模式之後Recommendations: After you change the recovery model

  • 在完整模式與大量記錄復原模式之間切換之後After switching between the full and bulk-logged recovery models

    • 在完成大量作業之後,立即切換回完整復原模式。After completing the bulk operations, immediately switch back to full recovery mode.

    • 從大量記錄復原模式切換回完整復原模式之後,請備份記錄檔。After switching from the bulk-logged recovery model back to the full recovery model, back up the log.

      注意: 您的備份策略維持不變:持續執行定期資料庫備份、記錄備份及差異備份。NOTE: Your backup strategy remains the same: continue performing periodic database, log, and differential backups.

  • 從簡單復原模式切換之後After switching from the simple recovery model

    • 在切換至完整復原模式或大量記錄復原模式的作業之後,立即建立完整或差異資料庫備份,以便啟動記錄鏈結。Immediately after switching to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.

      注意: 只有在第一次資料備份之後,切換到完整或大量記錄復原模式才會生效。NOTE: The switch to the full or bulk-logged recovery model takes effect only after the first data backup.

    • 排程定期記錄備份並據此更新還原計畫。Schedule regular log backups, and update your restore plan accordingly.

      重要!!!!IMPORTANT!!!! 備份記錄檔!!Back up your logs!! 如果您沒有經常備份記錄,交易記錄會一直擴充,直到磁碟空間用盡為止!If you do not back up the log frequently enough, the transaction log can expand until it runs out of disk space!

  • 切換到簡單復原模式之後After switching to the simple recovery model

    • 中止備份交易記錄的任何排程作業Discontinue any scheduled jobs for backing up the transaction log.

    • 確定已排程定期資料庫備份。Ensure periodic database backups are scheduled. 備份資料庫是必要的動作,才能保護資料及截斷交易記錄中非使用中的部分。Backing up your database is essential both to protect your data and to truncate the inactive portion of the transaction log.

Related tasksRelated tasks

相關內容Related Content

另請參閱See Also

復原模式 (SQL Server) Recovery Models (SQL Server)
交易記錄 (SQL Server) The Transaction Log (SQL Server)
ALTER DATABASE (Transact-SQL) ALTER DATABASE (Transact-SQL)
sys.databases (Transact-SQL) sys.databases (Transact-SQL)
復原模式 (SQL Server)Recovery Models (SQL Server)