在备份或还原期间启用或禁用备份校验和 (SQL Server)Enable or Disable Backup Checksums During Backup or Restore (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

本主题说明当备份或还原数据库时如何使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中启用或禁用备份校验和。This topic describes how to enable or disable backup checksums when you are backing up or restoring a database in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

本主题内容In This Topic

开始之前Before You Begin

SecuritySecurity

权限Permissions

BACKUPBACKUP
默认情况下,为 sysadmin 固定服务器角色以及 db_ownerdb_backupoperator 固定数据库角色的成员授予 BACKUP DATABASE 和 BACKUP LOG 权限。BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

备份设备的物理文件的所有权和权限问题可能会妨碍备份操作。Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL ServerSQL Server 必须能够读取和写入设备;运行 SQL ServerSQL Server 服务的帐户必须具有写入权限。must be able to read and write to the device; the account under which the SQL ServerSQL Server service runs must have write permissions. 但是,用于在系统表中为备份设备添加项目的 sp_addumpdevice不检查文件访问权限。However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. 备份设备物理文件的这些问题可能直到为备份或还原而访问物理资源时才会出现。Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.

RESTORERESTORE
如果不存在要还原的数据库,则用户必须有 CREATE DATABASE 权限才能执行 RESTORE。If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. 如果数据库存在,则 RESTORE 权限默认授予 sysadmindbcreator 固定服务器角色成员以及数据库的所有者 (dbo)(对于 FROM DATABASE_SNAPSHOT 选项,数据库始终存在)。If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).

RESTORE 权限被授予那些成员身份信息始终可由服务器使用的角色。RESTORE permissions are given to roles in which membership information is always readily available to the server. 因为只有在固定数据库可以访问且没有损坏时(在执行 RESTORE 时并不会总是这样)才能检查固定数据库角色成员身份,所以 db_owner 固定数据库角色成员没有 RESTORE 权限。Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

使用 SQL Server Management StudioUsing SQL Server Management Studio

在备份操作期间启用或禁用校验和To enable or disable checksums during a backup operation

  1. 执行以下步骤以便 创建数据库备份Follow the steps to create a database backup.

  2. “选项” 页的 “可靠性” 部分中,单击 “写入介质前检查校验和”On the Options page, in the Reliability section, click Perform checksum before writing to media.

使用 Transact-SQLUsing Transact-SQL

为备份操作启用或禁用备份校验和To enable or disable backup checksum for a backup operation

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 若要启用备份校验和,请在 BACKUP 语句中指定 WITH CHECKSUM 选项。To enable backup checksums in a BACKUP statement, specify the WITH CHECKSUM option. 若要禁用备份校验和,请指定 WITH NO_CHECKSUM 选项。To disable backup checksums, specify the WITH NO_CHECKSUM option. 这是默认行为,但压缩备份除外。This is the default behavior, except for a compressed backup. 下面的示例指定执行校验和。The following example specifies that checksums be performed.

BACKUP DATABASE AdventureWorks2012   
 TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'  
   WITH CHECKSUM;  
GO  

为还原操作启用或禁用备份校验和To enable or disable backup checksum for a restore operation

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 若要启用备份校验和,请在 RESTORE 语句中指定 WITH CHECKSUM 选项。To enable backup checksums in a RESTORE statement, specify the WITH CHECKSUM option. 这是压缩备份的默认行为。This is the default behavior for a compressed backup. 若要禁用备份校验和,请指定 WITH NO_CHECKSUM 选项。To disable backup checksums, specify the WITH NO_CHECKSUM option. 这是默认行为,但压缩备份除外。This is the default behavior, except for a compressed backup. 下面的示例指定执行备份校验和。The following example specifies that backup checksums be performed.

RESTORE DATABASE AdventureWorks2012   
 FROM DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'  
   WITH CHECKSUM;  
GO  

警告

在明确请求 CHECKSUM 进行还原操作并且备份包含备份校验和时,与默认情况相同,将同时验证备份校验和及页校验和。If you explicitly request CHECKSUM for a restore operation and if the backup contains backup checksums, backup checksums and page checksums are both verified, as in the default case. 但是,如果备份集不包含备份校验和,还原操作将失败,并显示一条消息指明校验和不存在。However, if the backup set lacks backup checksums, the restore operation fails with a message indicating that checksums are not present.

另请参阅See Also

RESTORE FILELISTONLY (Transact-SQL) RESTORE FILELISTONLY (Transact-SQL)
RESTORE HEADERONLY (Transact-SQL) RESTORE HEADERONLY (Transact-SQL)
RESTORE LABELONLY (Transact-SQL) RESTORE LABELONLY (Transact-SQL)
RESTORE VERIFYONLY (Transact-SQL) RESTORE VERIFYONLY (Transact-SQL)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
backupset (Transact-SQL) backupset (Transact-SQL)
RESTORE 参数 (Transact-SQL) RESTORE Arguments (Transact-SQL)
备份和还原期间可能出现的媒体错误 (SQL Server) Possible Media Errors During Backup and Restore (SQL Server)
指定备份或还原操作在遇到错误后是继续还是停止 (SQL Server)Specify Whether a Backup or Restore Operation Continues or Stops After Encountering an Error (SQL Server)