SQL Server
United States (English) Sign in
Home 2012 2008 R2 2008 Previous Versions Library Forums
This topic has not yet been rated - Rate this topic

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

SQL Server 2012

This topic describes how to specify whether a backup or restore operation continues or stops after encountering an error in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

In This Topic

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.

Arrow icon used with Back to Top link [Top]

To specify whether backup continues or stops after an error is encountered

  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 and Continue on error.

Arrow icon used with Back to Top link [Top]

To specify whether a backup operation continues or stops after encountering an error

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. In the BACKUP statement, specify the CONTINUE_AFTER ERROR option to continue or the STOP_ON_ERROR option to stop. The default behavior is to stop after encountering an error. This example instructs the backup operation to continue despite encountering an error.

Transact-SQL
Copy 
BACKUP DATABASE AdventureWorks2012 
 TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
   WITH CHECKSUM, CONTINUE_AFTER_ERROR;
GO

To specify whether a restore operation continues or stops after encountering an error

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. In the RESTORE statement, specify the CONTINUE_AFTER ERROR option to continue or the STOP_ON_ERROR option to stop. The default behavior is to stop after encountering an error. This example instructs the restore operation to continue despite encountering an error.

Transact-SQL
Copy 

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

Arrow icon used with Back to Top link [Top]

Did you find this helpful?
(1500 characters remaining)

Community Additions

ADD
© 2013 Microsoft. All rights reserved.