完整数据库备份 (SQL Server)Full Database Backups (SQL Server)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) 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. 因此,对于大型数据库而言,您可以用一系列“差异数据库备份” 来补充完整数据库备份。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