April 30, 2013 at 9:27 am
Dear expert,
I did lots of googlings and most suggestions pointing to a corrupted database, but I still have some doubt about if the database is really being corrupted.
A client created a backup of their database in SQL server 2005 and shipped to us from another state. When I tried to restore on SQL server 2008 R2 (I also tried on SQL server 2005 as well). Restoration failed. When I run the following query against the backup file, it returned databasename as "*** INCOMPLETE ***" and the rest of the fields are NULLs, except Position has a value of 1.
RESTORE HEADERONLY
FROM DISK='C:\Data\Database\SQLBackup\ClientDBName.bak'
GO
When I ran the following query,
RESTORE VERIFYONLY
FROM DISK = 'C:\Data\Database\SQLBackup\ClientDBName.bak'
GO
I got this
Msg 3242, Level 16, State 2, Line 2
The file on device 'C:\Data\Database\SQLBackup\ClientDBName.bak' is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Line 2
VERIFY DATABASE is terminating abnormally.
I asked client resending me the backup copy with CHECKSUM option turned on but still no luck. I tried to restore with CONTINUE_AFTER_ERROR and still unsuccessful.
Thanks in advance for any clue.
April 30, 2013 at 11:44 am
I would verify with the client that they are not backing up to multiple files and that they are actually using native SQL backup. If they are not using a native backup - then you need to either convert it and get the utility they are using.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 8, 2015 at 4:35 pm
Did you ever get to the bottom of this issue?
I'm having a similar problem.
SQL Backup of DB to .bak file and subsequent restore works.
If the .BAK file is now 'backed up' to our Networker environment and then restored to disk we are unable to use SQL restore on the 'recovered' .bak file.
VerifyHeader shows "*** INCOMPLETE ***".
Just by the way, the .BAK file is +/- 165GB in size.
Another thing, this is happening on a SQL 2012 Standard Edition system
March 9, 2015 at 5:04 am
Paul, a few questions to start us off:-
1. Is this a native SQL backup or does it use something like LiteSpeed?
2. Compressed or not?
3. What file backup solution are you using; just native Windows file copy jobs or something like BackupExec?
3. Are the version of Windows Server and SQL Server identical and equally-patched on both servers?
4. Does the original database contain more than one data file that is being backed up separately and may have been missed?
5. Is this a one-off problem and you regularly test backups which are usually OK, or does this always happen?
March 9, 2015 at 2:40 pm
1. Is this a native SQL backup or does it use something like LiteSpeed?
Standard SQL Server 2012 backup.
But we are running in compatibily SQL Server 2005 (90)
2. Compressed or not?
Not compressed
3. What file backup solution are you using; just native Windows file copy jobs or something like BackupExec?
Backup solution is EMC Networker 8.2
3. Are the version of Windows Server and SQL Server identical and equally-patched on both servers?
Problem exists even when running on the original source SQL Server
4. Does the original database contain more than one data file that is being backed up separately and may have been missed?
Only 1 data file and in Simple mode
5. Is this a one-off problem and you regularly test backups which are usually OK, or does this always happen?
This seems to be a one off.
The Server that this runs on typically doesn't have enough disk to test the local Backups
March 10, 2015 at 3:59 am
Hmmm.
The reason for my questions was mainly because I have seen something similar in the past with SQL Server 2008 R2, using LiteSpeed for the backups, and BackupExec for the actual file backups.
With that system, I would do a file restore from tape of the BackupExec file that contained my SQL backup and stick it on a server somewhere for use later.
When "later" came, the backup file seemed to have become "corrupted" somehow, and was unusable.
Grabbing a fresh copy from tape and using it straight away was absolutely fine and proved that both the SQL backup and the BackupExec backup were in fact perfectly good files.
It was almost as if the act of storing it on a server messed it up somehow.
I just hoped that your setup might be similar to the one I had these problems with, but I don't have any experience with your specific backup solution, and your answers to the more SQL-oriented questions don't give me any cause for concern at all.
I'll have a think...
Edit:-
Paul, take a look at This
March 10, 2015 at 6:01 pm
txtPost_CommentEmoticon(':-)'); Thanks for the info.
Will be webexing with our Networker product support tomorrow hopefully they can sort this out.http://www.sqlservercentral.com/Forums/Skins/Classic/Dialogs/InsertEmotIcon.aspx?ControlID=txtPost#
September 17, 2020 at 3:26 pm
I know this is an old post but I just ran into a similar issue. There were actually two problems: 1) the user under which the backup was performed did not have proper permissions to the master database, causing the backup to be "incomplete"; 2) I was trying to restore the backup using a version of SQL that was older than the version used to create the backup. In this case the RESTORE HEADERONLY still showed incomplete. When I switched to a compatible version the restore worked just fine.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply