Wednesday, December 17, 2008

The medium on device '.bak' expires on and cannot be overwritten. BACKUP DATABASE is terminating abnormally.

This is know issue and I'm just giving the workaround/solutions when someone encounters this error.

Error Message:
Msg 4030, Level 16, State 1, Line 1
The medium on device 'ABC.bak' expires on Mar 12 2009 9:49:52:000AM and cannot be overwritten.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Root Cause:
You are trying to append/overwrite a new backup to existing .bak backup file for which expiration date is set.

Temporary Solution:
You can try using INIT,SKIP Parameters in your backup command which will initialise the backup file (delete existing backup data) and skip the check for expiry date.

Permanent Fix:
Move existing backup to TAPE and delete from harddisk and then take new backup to same location.

Some useful parameters in BACKUP command(from BOL):
NOSKIP
Instructs the BACKUP statement to check the expiration date of all backup sets on the media before allowing them to be overwritten.
SKIP
Disables the backup set expiration and name checking usually performed by the BACKUP statement to prevent overwrites of backup sets. For more information, see the Remarks section.

INIT
Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set data on that device is overwritten.
The backup media is not overwritten if any one of the following conditions is met:
All backup sets on the media have not yet expired.
The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media.
Use the SKIP option to override these checks. For more information about interactions when using SKIP, NOSKIP, INIT, and NOINIT
Note If the backup media is password protected, SQL Server does not write to the media unless the media password is supplied. This check is not overridden by the SKIP option. Password-protected media may be overwritten only by reformatting it. For more information, see the FORMAT option.
NOINIT
Indicates that the backup set is appended to the specified disk or tape device, preserving existing backup sets. NOINIT is the default.

No comments:

Post a Comment

Please do not spam!