SQL Server 資料庫的備份與還原Back Up and Restore of SQL Server Databases

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

此文章說明備份 SQL ServerSQL Server 資料庫的優點、基本備份和還原詞彙,並介紹 SQL ServerSQL Server 的備份和還原策略,以及 SQL ServerSQL Server 備份和還原的安全性考量。This article describes the benefits of backing up SQL ServerSQL Server databases, basic backup and restore terms, and introduces backup and restore strategies for SQL ServerSQL Server and security considerations for SQL ServerSQL Server backup and restore.

此文章介紹 SQL Server 備份。This article introduces SQL Server backups. 如需備份 SQL Server 資料庫的特定步驟,請參閱建立備份For specific steps to back up SQL Server databases, see Creating backups.

SQL Server 備份與還原元件提供基本的防護措施,可保護 SQL ServerSQL Server 資料庫中所儲存的重要資料。The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in your SQL ServerSQL Server databases. 若要將重大資料遺失的風險降到最低,則需要定期備份資料庫,以保留對資料的修改。To minimize the risk of catastrophic data loss, you need to back up your databases to preserve modifications to your data on a regular basis. 計畫完善的備份和還原策略,可協助保護資料庫免於因各種失敗造成損毀而遺失資料。A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. 藉由還原備份組再復原資料庫,以測試您的策略,讓您準備好有效因應損毀情況。Test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.

除了儲存備份的本機儲存體之外,SQL Server 也支援備份至與還原自 Azure Blob 儲存體服務。In addition to local storage for storing the backups, SQL Server also supports backup to and restore from the Azure Blob Storage Service. 如需詳細資訊,請參閱 使用 Microsoft Azure Blob 儲存體服務進行 SQL Server 備份及還原For more information, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service. 針對使用 Microsoft Azure Blob 儲存體服務儲存的資料庫檔案, SQL Server 2016 (13.x)SQL Server 2016 (13.x) 的 Azure 快照集選項,提供近乎即時的備份及更快速的還原。For database files stored using the Microsoft Azure Blob storage service, SQL Server 2016 (13.x)SQL Server 2016 (13.x) provides the option to use Azure snapshots for nearly instantaneous backups and faster restores. 如需詳細資訊,請參閱 Azure 中資料庫檔案的檔案快照集備份For more information, see File-Snapshot Backups for Database Files in Azure.

備份原因Why back up?

  • 備份 SQL ServerSQL Server 資料庫、針對備份執行測試還原程序,並將備份的複本儲存在安全的異地位置,即可避免可能發生的重大資料遺失。Backing up your SQL ServerSQL Server databases, running test restores procedures on your backups, and storing copies of backups in a safe, off-site location protects you from potentially catastrophic data loss. 備份是保護資料的的唯一方法。Backing up is the only way to protect your data.

    使用有效的資料庫備份,就可以從多種失敗中復原資料,例如:With valid backups of a database, you can recover your data from many failures, such as:

    • 媒體錯誤。Media failure.
    • 使用者錯誤 (例如不小心卸除資料表)。User errors, for example, dropping a table by mistake.
    • 硬體故障 (例如,磁碟機損壞或伺服器永久損毀)。Hardware failures, for example, a damaged disk drive or permanent loss of a server.
    • 天然災害。Natural disasters. 藉由對 Azure Blob 儲存體服務使用 SQL Server 備份,就可以在與內部部署位置不同的區域建立異地備份,以便在發生影響內部部署位置的天然災害事件時使用。By using SQL Server Backup to Azure Blob storage service, you can create an off-site backup in a different region than your on-premises location, to use in the event of a natural disaster affecting your on-premises location.
  • 此外,資料庫備份對於例行管理很有用,例如,將資料庫從一部伺服器複製到另一部伺服器、設定 AlwaysOn 可用性群組Always On availability groups 或資料庫鏡像,以及封存。Additionally, backups of a database are useful for routine administrative purposes, such as copying a database from one server to another, setting up AlwaysOn 可用性群組Always On availability groups or database mirroring, and archiving.

備份詞彙表Glossary of backup terms

備份 [動詞]back up [verb]
建立備份 [名詞] 的過程,就是從 SQL ServerSQL Server 資料庫複製資料錄,或從它的交易記錄檔複製記錄檔記錄。The process of creating a backup [noun] by copying data records from a SQL ServerSQL Server database, or log records from its transaction log.

備份 [名詞]backup [noun]
失敗後可用來還原和復原資料的資料複本。A copy of data that can be used to restore and recover the data after a failure. 資料庫備份也可用來將資料庫的複本還原到新位置。Backups of a database can also be used to restore a copy the database to a new location.

備份 裝置backup device
寫入 SQL Server 備份並從中進行還原的磁碟或磁帶裝置。A disk or tape device to which SQL Server backups are written and from which they can be restored. SQL Server 備份也可以寫入 Azure Blob 儲存體服務,而且會使用 URL 格式來指定備份檔案的目的地和名稱。SQL Server backups can also be written to an Azure Blob storage service, and URL format is used to specify the destination and the name of the backup file.. 如需詳細資訊,請參閱 使用 Microsoft Azure Blob 儲存體服務進行 SQL Server 備份及還原For more information, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.

備份媒體backup media
已寫入一或多個備份的一或多個磁帶或磁碟檔案。One or more tapes or disk files to which one or more backups have been written.

資料備份data backup
整個資料庫 (資料庫備份)、部分資料庫 (部分備份) 或是一組資料檔案或檔案群組 (檔案備份) 中資料的備份。A backup of data in a complete database (a database backup), a partial database (a partial backup), or a set of data files or filegroups (a file backup).

資料庫備份database backup
資料庫的備份。A backup of a database. 完整資料庫備份代表備份完成時的整個資料庫。Full database backups represent the whole database at the time the backup finished. 差異資料庫備份僅包含自其最近的完整資料庫備份以來,對資料庫所做的變更。Differential database backups contain only changes made to the database since its most recent full database backup.

差異備份differential backup
一種資料備份,是以整個或部分資料庫或一組資料檔案或檔案群組 (差異基底) 的最新完整備份為基礎,而且只包含自該基底以來變更的資料。A data backup that is based on the latest full backup of a complete or partial database or a set of data files or filegroups (the differential base) and that contains only the data that has changed since that base.

完整備份full backup
一種資料備份,包含特定資料庫或一組檔案群組或檔案中的所有資料,也包含足以讓這個資料復原的記錄。A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.

記錄備份log backup
交易記錄的備份,包含先前的記錄備份中未備份的所有記錄。A backup of transaction logs that includes all log records that were not backed up in a previous log backup. (完整復原模式)(full recovery model)

復原 (recover)recover
將資料庫回復為穩定且一致的狀態。To return a database to a stable and consistent state.

recoveryrecovery
讓資料庫進入交易一致狀態的資料庫啟動階段或含復原之還原的階段。A phase of database startup or of a restore with recovery that brings the database into a transaction-consistent state.

復原模式recovery model
控制資料庫上交易記錄維護的資料庫屬性。A database property that controls transaction log maintenance on a database. 復原模式共有三種:簡單、完整和大量記錄。Three recovery models exist: simple, full, and bulk-logged. 資料庫的復原模式決定其備份和還原需求。The recovery model of database determines its backup and restore requirements.

還原restore
一個多階段的程序,它會將指定之 SQL ServerSQL Server 備份中的所有資料和記錄頁面複製到指定的資料庫中,然後藉由套用所記錄的變更取得前面時段的資料,向前復原備份中記錄的所有交易。A multi-phase process that copies all the data and log pages from a specified SQL ServerSQL Server backup to a specified database, and then rolls forward all the transactions that are logged in the backup by applying logged changes to bring the data forward in time.

備份與還原策略Backup and restore strategies

備份和還原資料作業必須依特定環境自訂,而且也必須使用可用的資源。Backing up and restoring data must be customized to a particular environment and must work with the available resources. 因此,為了可靠地使用備份和還原作業進行復原,您需要擬定備份和還原策略。Therefore, a reliable use of backup and restore for recovery requires a backup and restore strategy. 設計良好的備份和還原策略可充分提高資料可用性,並使資料損失降至最少,同時考慮到您的特定業務需求。A well-designed backup and restore strategy maximizes data availability and minimizes data loss, while considering your particular business requirements.

重要

請將資料庫和備份放置於不同的裝置上。Place the database and backups on separate devices. 否則,如果包含資料庫的裝置故障,備份將無法使用。Otherwise, if the device containing the database fails, your backups will be unavailable. 將資料和備份放置於不同的裝置,也可以針對寫入備份和資料庫生產使用強化 I/O 效能。**Placing the data and backups on separate devices also enhances the I/O performance for both writing backups and the production use of the database.**

備份和還原策略包含備份部分與還原部分。A backup and restore strategy contains a backup portion and a restore portion. 策略的備份部分定義備份的類型和頻率、所需硬體的本質和速度、測試備份的方法,以及儲存備份媒體的位置與方法 (包括安全性考量)。The backup part of the strategy defines the type and frequency of backups, the nature, and speed of the hardware that is required for them, how backups are to be tested, and where and how backup media is to be stored (including security considerations). 策略的還原部分定義誰負責執行還原,以及應該如何執行還原,以達到資料庫可用性並將資料損失降到最低的目標。The restore part of the strategy defines who is responsible for performing restores and how restores should be performed to meet your goals for availability of the database and for minimizing data loss. 建議您寫下備份和還原程序,並將文件的副本保留在執行書中。We recommend that you document your backup and restore procedures and keep a copy of the documentation in your run book.

設計有效的備份和還原策略需要仔細計畫、實作及測試。Designing an effective backup and restore strategy requires careful planning, implementation, and testing. 測試是必要的。Testing is required. 在利用還原策略中包含的所有備份組合順利完成還原之前,您還稱不上有備份策略。You do not have a backup strategy until you have successfully restored backups in all the combinations that are included in your restore strategy. 您必須考慮各種因素。You must consider a variety of factors. 這些選項包括:These include the following:

  • 您的組織對於資料庫的生產目標,特別是對資料可用性以及保護資料免於遺失的需求。The production goals of your organization for the databases, especially the requirements for availability and protection of data from loss.

  • 您的每一個資料庫的本質:其大小、使用模式、內容本質及資料需求等等。The nature of each of your databases: its size, its usage patterns, the nature of its content, the requirements for its data, and so on.

  • 相關資源的限制,例如:硬體、人員、儲存備份媒體的空間、儲存媒體的實體安全性等等。Constraints on resources, such as: hardware, personnel, space for storing backup media, the physical security of the stored media, and so on.

復原模式對備份和還原的影響Impact of the recovery model on backup and restore

備份和還原作業是在復原模式的內容中發生。Backup and restore operations occur within the context of a recovery model. 復原模式是控制交易記錄管理方式的資料庫屬性。A recovery model is a database property that controls how the transaction log is managed. 此外,資料庫的復原模式也將決定資料庫所支援的備份類型與還原實例。Also, the recovery model of a database determines what types of backups and what restore scenarios are supported for the database. 一般而言,資料庫會使用完整復原模式或簡單復原模式。Typically a database uses either the simple recovery model or the full recovery model. 完整復原模式可以藉由在大量作業之前切換到大量記錄復原模式,補充其功能。The full recovery model can be supplemented by switching to the bulk-logged recovery model before bulk operations. 如需這些復原模式及其對交易記錄管理之影響的簡介,請參閱 交易記錄 (SQL Server)For an introduction to these recovery models and how they affect transaction log management, see The Transaction Log (SQL Server)

資料庫復原模式的最佳選擇視您的商務需求而定。The best choice of recovery model for the database depends on your business requirements. 若要避免管理交易記錄,並簡化備份和還原,請使用簡單復原模式。To avoid transaction log management and simplify backup and restore, use the simple recovery model. 若要將遺失工作的風險降到最低 (但會耗用管理負擔成本),請使用完整復原模式。To minimize work-loss exposure, at the cost of administrative overhead, use the full recovery model. 如需復原模式對備份與還原之影響的資訊,請參閱 備份概觀 (SQL Server)For information about the effect of recovery models on backup and restore, see Backup Overview (SQL Server).

設計備份策略Design your backup strategy

在為特定資料庫選擇符合商務需求的復原模式之後,您必須規劃並實作對應的備份策略。After you have selected a recovery model that meets your business requirements for a specific database, you have to plan and implement a corresponding backup strategy. 最佳備份策略取決於各種因素,其中特別重要的是下列項目:The optimal backup strategy depends on a variety of factors, of which the following are especially significant:

  • 應用程式每天必須花多少時間來存取資料庫?How many hours a day do applications have to access the database?

    如果可預測離峰期間,則建議您排定該期間內完整資料庫備份的作業時程。If there is a predictable off-peak period, we recommend that you schedule full database backups for that period.

  • 可能發生變更和更新的頻率為何?How frequently are changes and updates likely to occur?

    如果經常變更,請考慮下列作法:If changes are frequent, consider the following:

    • 在簡單復原模式下,請考慮在完整資料庫備份之間排程差異備份。Under the simple recovery model, consider scheduling differential backups between full database backups. 差異備份只會擷取最後一次完整資料庫備份之後所發生的變更。A differential backup captures only the changes since the last full database backup.

    • 在完整復原模式下,您應排程經常的記錄備份。Under the full recovery model, you should schedule frequent log backups. 在完整備份之間排定差異備份,可減少您在還原資料後必須還原的記錄備份數目,從而減少還原時間。Scheduling differential backups between full backups can reduce restore time by reducing the number of log backups you have to restore after restoring the data.

  • 變更可能會發生在資料庫的一小部分,還是資料庫的大部分?Are changes likely to occur in only a small part of the database or in a large part of the database?

    如果是大型資料庫,且其變更集中於檔案或檔案群組部分,則部分備份及 (或) 檔案備份可能十分有用。For a large database in which changes are concentrated in a part of the files or filegroups, partial backups and or file backups can be useful. 如需詳細資訊,請參閱部分備份 (SQL Server)完整檔案備份 (SQL Server)For more information, see Partial Backups (SQL Server) and Full File Backups (SQL Server).

  • 完整資料庫備份需要多少磁碟空間?How much disk space will a full database backup require?

估計完整資料庫備份的大小Estimate the size of a full database backup

在實作備份和還原策略前,您必須估計完整資料庫備份將使用多少磁碟空間。Before you implement a backup and restore strategy, you should estimate how much disk space a full database backup will use. 備份作業會將資料庫中的資料複製到備份檔中。The backup operation copies the data in the database to the backup file. 備份僅包含資料庫中的實際資料,而不包含任何未使用的空間。The backup contains only the actual data in the database and not any unused space. 因此,備份通常會比資料庫本身還小。Therefore, the backup is usually smaller than the database itself. 您可以使用 sp_spaceused 系統預存程序來估計完整資料庫備份的大小。You can estimate the size of a full database backup by using the sp_spaceused system stored procedure. 如需詳細資訊,請參閱 sp_spaceused (Transact-SQL)For more information, see sp_spaceused (Transact-SQL).

排程備份Schedule backups

執行備份作業對進行中的交易影響最小,所以可以在一般作業過程中執行備份作業。Performing a backup operation has minimal effect on transactions that are running; therefore, backup operations can be run during regular operations. 您可以執行 SQL ServerSQL Server 備份,並且將實際執行工作負載受到的影響降至最低。You can perform a SQL ServerSQL Server backup with minimal effect on production workloads.

如需備份期間之並行限制的資訊,請參閱 備份概觀 (SQL Server)For information about concurrency restrictions during backup, see Backup Overview (SQL Server).

決定您需要的備份類型以及執行每一類型所需的頻率之後,建議您將一般備份排程為資料庫之資料庫維護計畫的一部分。After you decide what types of backups you require and how frequently you have to perform each type, we recommend that you schedule regular backups as part of a database maintenance plan for the database. 如需有關維護計畫以及如何為資料庫備份和記錄備份建立這些計畫的詳細資訊,請參閱< Use the Maintenance Plan Wizard>。For information about maintenance plans and how to create them for database backups and log backups, see Use the Maintenance Plan Wizard.

測試備份!Test your backups!

在您完成備份的測試之前,還不能算是具備還原策略。You do not have a restore strategy until you have tested your backups. 您務必要將資料庫副本還原到測試系統,以針對每個資料庫完整測試備份策略。It is very important to thoroughly test your backup strategy for each of your databases by restoring a copy of the database onto a test system. 您必須測試還原您要使用的每個備份類型。You must test restoring every type of backup that you intend to use.

建議您維護每個資料庫的作業手冊。We recommend that you maintain an operations manual for each database. 這份作業手冊應記載備份位置、備份裝置名稱 (如果有的話),以及還原測試備份所需的時間量。This operations manual should document the location of the backups, backup device names (if any), and the amount of time that is required to restore the test backups.

使用 xEvent 監視進度Monitor progress with xEvent

由於資料庫的大小和涉及作業的複雜度,備份和還原作業可能需要相當長的時間。Backup and restore operations can take a considerable amount of time due to the size of a database and the complexity of the operations involved. 當任一項作業引發問題時,您可以使用 backup_restore_progress_trace 擴充事件來即時監視進度。When issues arise with either operation, you can use the backup_restore_progress_trace extended event to monitor progress live. 如需擴充事件的詳細資訊,請參閱擴充事件For more information about extended events, see extended events.

警告

使用 backup_restore_progress_trace 擴充事件可能會造成效能問題,而且會耗用大量的磁碟空間。Using the backup_restore_progress_trace extended event can cause a performance issue and consume a significant amount of disk space. 請於短時間內使用、小心執行,並且在生產環境中實作之前徹底測試。Use for short periods of time, exercise caution, and test thoroughly before implementing in production.

-- Create the backup_restore_progress_trace extended event esssion
CREATE EVENT SESSION [BackupRestoreTrace] ON SERVER 
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file(SET filename=N'BackupRestoreTrace')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

-- Start the event session  
ALTER EVENT SESSION [BackupRestoreTrace]  
ON SERVER  
STATE = start;  
GO  

-- Stop the event session  
ALTER EVENT SESSION [BackupRestoreTrace]  
ON SERVER  
STATE = stop;  
GO  

來自擴充事件的範例輸出Sample output from extended event

備份 xevent 輸出的範例 還原 xevent 輸出的範例Example of back up xevent output Example of restore xevent output

深入了解備份工作More about backup tasks

使用備份裝置和備份媒體Working with backup devices and backup media

建立備份Creating backups

注意!Note! 如果是部分備份或只複製備份,就必須分別搭配 PARTIAL 或 COPY_ONLY 選項來使用 Transact-SQLTransact-SQLBACKUP 陳述式。For partial or copy-only backups, you must use the Transact-SQLTransact-SQLBACKUP statement with the PARTIAL or COPY_ONLY option, respectively.

使用 SSMSUsing SSMS

使用 T-SQLUsing T-SQL

還原資料備份Restore data backups

使用 SSMSUsing SSMS

使用 T-SQLUsing T-SQL

還原交易記錄 (完整復原模式)Restore transaction logs (Full Recovery Model)

使用 SSMSUsing SSMS

使用 T-SQLUsing T-SQL

詳細資訊和資源More information and resources

備份概觀 (SQL Server) Backup Overview (SQL Server)
還原和復原概觀 (SQL Server) Restore and Recovery Overview (SQL Server)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
備份與還原 Analysis Services 資料庫 Backup and Restore of Analysis Services Databases
備份並還原全文檢索目錄與索引。 Back Up and Restore Full-Text Catalogs and Indexes
備份及還原複寫的資料庫 Back Up and Restore Replicated Databases
交易記錄 (SQL Server) The Transaction Log (SQL Server)
復原模式 (SQL Server) Recovery Models (SQL Server)
媒體集、媒體家族與備份組 (SQL Server)Media Sets, Media Families, and Backup Sets (SQL Server)