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

Restore TSQL

RESTORE DATABASE [databasename]
FROM DISK = N'c:\database.bak'
WITH MOVE 'databasename' TO 'D:\data\databasename.mdf',
MOVE 'databasename_log' TO 'E:\logs\databasename.ldf', NOUNLOAD, STATS = 10
GO

TSQL : Restoring a database to a point in time (Litespeed version)

Sql to restore LiteSpeed backups.

-- restore main bak first

exec master.dbo.xp_restore_database
@database = 'Accounts' ,
@filename = '\\FILESTORE\SQL$\Accounts\Accounts_20060502001507_Friday_LS.bak' ,
@filenumber = 1,
@with = 'NORECOVERY',
@with = 'NOUNLOAD',
@with = 'STATS = 10',
@with = 'REPLACE'
go

-- restore latest diff

exec master.dbo.xp_restore_database
@database = 'Accounts',
@filename = '\\FILESTORE\SQL$\Accounts\Accounts_20060502014913_Thursday.diff',
@filenumber = 1,
@with = 'NORECOVERY',
@with = 'NOUNLOAD',
@with = 'STATS = 10',
@with = 'REPLACE'
go

-- restore transaction log backups
-- repeat for each one, changing NORECOVERY to RECOVERY for the last one.

exec master.dbo.xp_restore_database
@database = 'Accounts',
@filename = '\\FILESTORE\SQL$\Accounts\Accounts_20060502060107_Thursday.trn',
@filenumber = 1,
@with = 'NORECOVERY',
@with = 'NOUNLOAD',
@with = 'STATS = 10',
@with = 'REPLACE'
go

TSQL : Restoring a database to a point in time

Sql to use once you have identified which backup files need to be restored...

-- restore last full backup
-- use NORECOVERY to state you want to add further backup files

RESTORE DATABASE Sales
FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060423180002_Wednesday_MS.bak'
WITH NORECOVERY

-- add last differential backup
-- use NORECOVERY to state you want to add further backup files

RESTORE DATABASE Sales
FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425010002_Friday.diff'
WITH NORECOVERY
go

-- apply transaction log backups , up until the point you wish to restore to
-- use NORECOVERY to state you want to add further backup files
-- on the final restore, omit WITH NORECOVERY or specify WITH RECOVERY so the db is readable.

RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425060000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425070000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425080000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425090000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425100000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425110000_Friday_MS.trn' WITH NORECOVERY
RESTORE LOG Sales FROM DISK = '\\FILESTORE\SQL$\Sales\Sales_20060425120000_Friday_MS.trn'
go