Enable or Disable Backup Checksums During Backup or Restore (SQL Server)

This topic describes how to enable or disable backup checksums when you are backing up or restoring a database in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

In This Topic

• Before you begin:

  Security

• To enable or disable backup checksums, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Security

Permissions

• BACKUP
  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 Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions. 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.

• RESTORE
  If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. 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 permissions are given to roles in which membership information is always readily available to the server. 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.

[Top]

Using 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.

[Top]

Using Transact-SQL

To enable or disable backup checksum for a backup operation

1. Connect to the Database Engine.

2. From the Standard bar, click New Query.

3. To enable backup checksums in a BACKUP statement, specify the WITH CHECKSUM option. 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. Connect to the Database Engine.

2. From the Standard bar, click New Query.

3. To enable backup checksums in a RESTORE statement, specify the WITH CHECKSUM option. This is the default behavior for a compressed backup. 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

Warning

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.

[Top]

See Also

Reference

RESTORE FILELISTONLY (Transact-SQL)

RESTORE HEADERONLY (Transact-SQL)

RESTORE LABELONLY (Transact-SQL)

RESTORE VERIFYONLY (Transact-SQL)

BACKUP (Transact-SQL)

backupset (Transact-SQL)

RESTORE Arguments (Transact-SQL)

Concepts

Possible Media Errors During Backup and Restore (SQL Server)

Specify Whether a Backup or Restore Operation Continues or Stops After Encountering an Error (SQL Server)