Friday, 22 August 2008

Backup / Restore Corruption

" Server: Msg 3013, Level 16, State 1, Line 1 The backup data at the end of 'devicename' is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets. Server: Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. "

The backup file being restored/written is damaged due to an error (write error during the backup or physical media error).
* This can occur when restoring from a network source too, hence maybe worth attempting to copy locally first.

If you're trying to backup to an existing file and don't care about the existing data (as you are overwriting it anyway) , replace it like this >

BACKUP DATABASE db1 TO DISK='C:\db1.bak' with FORMAT

If you're attempting a restore (gulp), it's a 2 step process.
1) Get the file number of the backup set within the backup file >

RESTORE HEADERONLY FROM DISK='C:\db1.bak'

2) Having identified a backup set (in this case number 3), try and restore it from the backup set manually >

RESTORE DATABASE db1 FROM DISK='C:\db1.bak WITH FILE = 3

No comments: