完整資料庫備份 (SQL Server)Full Database Backups (SQL Server)

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

完整資料庫備份會備份整個資料庫。A full database backup backs up the whole database. 這包括交易記錄的部分,讓完整資料庫得以在還原完整資料庫備份之後復原。This includes part of the transaction log so that the full database can be recovered after a full database backup is restored. 完整資料庫備份代表備份完成時的資料庫。Full database backups represent the database at the time the backup finished.

提示

資料庫的大小增加時,完整資料庫備份就需要更多的時間才能完成,同時也需要更多的儲存空間。As a database increases in size full database backups take more time to finish and require more storage space. 因此,若為大型資料庫,您可能會想透過一系列的 「差異資料庫備份」 (Differential database backups) 補充完整資料庫備份。Therefore, for a large database, you might want to supplement a full database backup with a series of differential database backups. 如需詳細資訊,請參閱 差異備份 (SQL Server)For more information, see Differential Backups (SQL Server).

重要

資料庫備份上的 TRUSTWORTHY 是設為 OFF。TRUSTWORTHY is set to OFF on a database backup. 如需如何將 TRUSTWORTHY 設成 ON 的資訊,請參閱 ALTER DATABASE SET 選項 (Transact-SQL)For information about how to set TRUSTWORTHY to ON, see ALTER DATABASE SET Options (Transact-SQL).

本主題內容:In This Topic:

簡單復原模式下的資料庫備份Database Backups Under the Simple Recovery Model

在簡單復原模式下,每次備份之後,如果會發生損毀,資料庫都將承受可能遺失工作的風險。Under the simple recovery model, after each backup, the database is exposed to potential work loss if a disaster were to occur. 工作遺失風險隨著每一次更新而增加,直到下次備份為止;此時工作遺失風險便會歸零,但是又會重新開始另一循環的工作遺失風險。The work-loss exposure increases with each update until the next backup, when the work-loss exposure returns to zero and a new cycle of work-loss exposure starts. 一段時間之後,備份之間的工作遺失風險會增加。Work-loss exposure increases over time between backups. 下圖顯示只使用完整資料庫備份之備份策略的工作遺失風險。The following illustration shows the work-loss exposure for a backup strategy that uses only full database backups.

顯示資料庫備份之間的工作損失風險Shows work-loss exposure between database backups

範例 (Transact-SQLTransact-SQL)Example ( Transact-SQLTransact-SQL)

下列範例顯示如何使用 WITH FORMAT 來覆寫任何現有備份並建立新的媒體集,以建立完整資料庫備份。The following example shows how to create a full database backup by using WITH FORMAT to overwrite any existing backups and create a new media set.

-- Back up the AdventureWorks2012 database to new media set.  
BACKUP DATABASE AdventureWorks2012  
    TO DISK = 'Z:\SQLServerBackups\AdventureWorksSimpleRM.bak'   
    WITH FORMAT;  
GO  

完整復原模式下的資料庫備份Database Backups Under the Full Recovery Model

若為使用完整和大量記錄復原的資料庫,資料庫備份是必要的,但還是不足夠。For databases that use full and bulk-logged recovery, database backups are necessary but not sufficient. 交易記錄備份也是必要的。Transaction log backups are also required. 下圖顯示完整復原模式下可行的最不複雜的備份策略。The following illustration shows the least complex backup strategy that is possible under the full recovery model.

完整資料庫備份和記錄備份系列Series of full database backups and log backups

如需如何建立記錄備份的相關資訊,請參閱交易記錄備份 (SQL Server)For information about how to create log backups, see Transaction Log Backups (SQL Server).

範例 (Transact-SQLTransact-SQL)Example ( Transact-SQLTransact-SQL)

下列範例顯示如何使用 WITH FORMAT 來覆寫任何現有備份並建立新的媒體集,以建立完整資料庫備份。The following example shows how to create a full database backup by using WITH FORMAT to overwrite any existing backups and create a new media set. 然後,此範例會接著備份交易記錄。Then, the example backs up the transaction log. 在實際的情況下,您必須執行一連串的定期記錄備份。In a real-life situation, you would have to perform a series of regular log backups. 就此範例而言, AdventureWorks2012AdventureWorks2012 範例資料庫會設定為使用完整復原模式。For this example, the AdventureWorks2012AdventureWorks2012 sample database is set to use the full recovery model.

USE master;  
ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;  
GO  
-- Back up the AdventureWorks2012 database to new media set (backup set 1).  
BACKUP DATABASE AdventureWorks2012  
  TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012FullRM.bak'   
  WITH FORMAT;  
GO  
--Create a routine log backup (backup set 2).  
BACKUP LOG AdventureWorks2012 TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012FullRM.bak';  
GO  

使用完整資料庫備份來還原資料庫Use a Full Database Backup to Restore the Database

您可以將資料庫從完整資料庫備份還原到任何位置,在一個步驟中重新建立整個資料庫。You can re-create a whole database in one step by restoring the database from a full database backup to any location. 在備份中包含足夠的交易記錄,以讓您將資料庫復原至在備份完成時的時間點。Enough of the transaction log is included in the backup to let you recover the database to the time when the backup finished. 還原的資料庫會符合資料庫備份完成時的原本狀態,再扣除掉任何未認可的交易。The restored database matches the state of the original database when the database backup finished, minus any uncommitted transactions. 在完整復原模式之下,應該接著還原所有後續的交易記錄備份。Under the full recovery model, you should then restore all subsequent transaction log backups. 在資料庫復原之後,會回復未認可的交易。When the database is recovered, uncommitted transactions are rolled back.

如需詳細資訊,請參閱完整資料庫還原 (簡單復原模式)完整資料庫還原 (完整復原模式)For more information, see Complete Database Restores (Simple Recovery Model) or Complete Database Restores (Full Recovery Model).

相關工作Related Tasks

建立完整資料庫備份To create a full database backup

排程備份作業To schedule backup jobs

使用維護計畫精靈Use the Maintenance Plan Wizard

另請參閱See Also

SQL Server 資料庫的備份與還原 Back Up and Restore of SQL Server Databases
備份概觀 (SQL Server) Backup Overview (SQL Server)
備份與還原 Analysis Services 資料庫Backup and Restore of Analysis Services Databases