Backup file has BackupName of "*** INCOMPLETE ***" as a result of RESTORE HEADERONLY
Question
Monday, August 17, 2009 8:39 AM
Hi!
I have a full backup which is devided by 3 files, generated from 1 database (for the time being DB001).
I am trying to make a new copy of DB001 in another server using the 3 backup files.
Firstly I tried RESTORE DATABASE on Query Analyzer but it failed with the following message;
**
Server: Msg 3270, Level 16, State 1, Line 1
An internal consistency error occurred. Contact Technical Support for assistance
**
also in SQL server log;
**The backup data in 'D:\DB001_bk02.bak' is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable.
**
Then I looked for a solution and found following T-SQL can be helpful;
**
RESTORE HEADERONLY from**
**disk = 'D:\DB001_bk01.bak',
disk = 'D:\DB001_bk02.bak',
disk = 'D:\DB001_bk03.bak'
**and the result showed;
BackupName is "*** INCOMPLETE ***",
Position is "1",
other fields are "NULL".
An unrelated full backup which I guess it is no problem has
meaningful values in BackupName and other fields.
Has anyone seen this header?
How can I extract the data from the "incomplete" files? (SQL server log indicates the data is usable...)
Thank you in advance!
YS
All replies (5)
Tuesday, August 18, 2009 2:27 PM âś…Answered
Yes, your existing backup is corrupt - take another one. The N is making the pathname unicode.Managing Director, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet Magazine, Author of 2005 DBCC CHECKDB/repair, Course author/instructor of Microsoft Certified Master - Database.
Tuesday, August 18, 2009 2:30 AM
This looks like your backup file has become corrupt and you're not going to be able to salvage the backup that you want. Are you on 2000 or 2005? On 2005 you may be able to use the WITH CONTINUE_AFTER_ERROR syntax if it's really critical that you get this backup restored. If not on 2005, you can't do anything.
ThanksManaging Director, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet Magazine, Author of 2005 DBCC CHECKDB/repair, Course author/instructor of Microsoft Certified Master - Database.
Tuesday, August 18, 2009 4:35 AM
Hi
Adding to PaulRandal
You can verify your backup good or bad by using .
RESTORE VERIFYONLY FROM DISK = N'<Path>'
Please let us know any help required.
Thanks
SNIVAS
Tuesday, August 18, 2009 8:29 AM
Hi!,
Thank you, Paul and SNIVAS.
I'm on MS SQL Server 2000 unfortunately. Do you think backup should be taken again?
Meanwhile I have one question about syntax. What does "N" stand for at the path part?
Thank you experts!
YS
Thursday, August 20, 2009 8:33 AM
Hi
I tried RESTORE VERIFYONLY resulted following error;
**Server: Msg 3242, Level 16, State 2, Line 1,
The file on device 'D:\DB001_bk02.bak' is not a valid Microsoft tape Format backup set.
**
Maybe Paul is right. I have to give up salvaging data...
Thank you for your kind attention!
YS