November 16, 2011 at 3:39 pm
Hi,
Has anyone had issues with compressed backups not restoring? I administer ~45 databases and occasionally restore backups, normal thing to do. We upgraded to Sql 2008 R2 and I enabled backup compression. I use Robocopy to chunk backups around.
The first time I had to restore I get this:
10 percent processed.
20 percent processed.
30 percent processed.
Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (0:0) in database "zCustDataFromUhiBak" as read from the backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I hem and haw, try a bunch of stuff, CONTINUE_AFTER_ERROR, yada, yada. no go. I do a test restore on 7 databases on different server, all fail miserably.
RESTORE VERIFYONLY FROM DISK returns the data is invalid.
They restore no problem on the original server and a couple actually restore if I manually copy over the backups.
I turn off compression, it works. Anyone else experience this?
Thanks,
Steve
November 16, 2011 at 11:05 pm
You cannot restore the backup of a compressed database to an uncompressed database.
Few other restrictions:
•Compressed and uncompressed backups cannot co-exist in a media set.
•Previous versions of SQL Server cannot read compressed backups.
•NTbackups cannot share a tape with compressed SQL Server backups.
November 17, 2011 at 2:50 am
Dev (11/16/2011)
You cannot restore the backup of a compressed database to an uncompressed database.
I didn't know this one. Can you please clarify what you mean with "compressed database"? Page/Row compression in place?
-- Gianluca Sartori
November 17, 2011 at 3:31 am
stevescanlan (11/16/2011)
I hem and haw, try a bunch of stuff, CONTINUE_AFTER_ERROR, yada, yada. no go. I do a test restore on 7 databases on different server, all fail miserably.RESTORE VERIFYONLY FROM DISK returns the data is invalid.
They restore no problem on the original server and a couple actually restore if I manually copy over the backups.
Sounds like file damage while copying. It happens, and has nothing to do with compression. Could be you just hit network glitches. In that case it's usually recommended to take another backup or copy again
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2011 at 6:14 am
I examined the files and the size is the same. There is a little variation in the size on disk, but that shouldn't matter.
November 17, 2011 at 6:16 am
You cannot restore the backup of a compressed database to an uncompressed database.
I am doing the entire database to a new database.
Few other restrictions:
•Compressed and uncompressed backups cannot co-exist in a media set.
•Previous versions of SQL Server cannot read compressed backups.
•NTbackups cannot share a tape with compressed SQL Server backups.
There is only one media set
Same Sql version, Sql 2008 R2
No tapes involved, strictly hard drive files.
November 17, 2011 at 6:24 am
stevescanlan (11/17/2011)
You cannot restore the backup of a compressed database to an uncompressed database.I am doing the entire database to a new database.
Don't worry about that statement. While it's anything but clear what it means, I do have confirmation that it's in reference to database compression somehow, nothing whatsoever to do with backup compression
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2011 at 6:25 am
stevescanlan (11/17/2011)
I examined the files and the size is the same. There is a little variation in the size on disk, but that shouldn't matter.
Not sure what comment this is in reply to, as no one mentioned file size.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2011 at 6:33 am
GilaMonster (11/17/2011)
stevescanlan (11/17/2011)
I examined the files and the size is the same. There is a little variation in the size on disk, but that shouldn't matter.Not sure what comment this is in reply to, as no one mentioned file size.
One of the posts mentioned corruption while copying over the network, thought I'd toss the file size info into the mix.
November 17, 2011 at 6:53 am
stevescanlan (11/17/2011)
GilaMonster (11/17/2011)
stevescanlan (11/17/2011)
I examined the files and the size is the same. There is a little variation in the size on disk, but that shouldn't matter.Not sure what comment this is in reply to, as no one mentioned file size.
One of the posts mentioned corruption while copying over the network, thought I'd toss the file size info into the mix.
Yes, I did. Short of an unexpected truncation that won't change the file size. (and that would give a different error)
I did a couple searches. The only other cases I saw of this were tracked down to faulty network cables or fibre connections. It's the most likely cause since the backup restores fine on the original server and not once copied.
What you could do is zip or rar the file pre-copy as that will add a CRC to the compressed file. Otherwise you could take the backup WITH CHECKSUM which will immediately fail the restore if the file's checksum is found to be incorrect (as would happen in a mangled copy)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2011 at 7:02 am
Gianluca Sartori (11/17/2011)
Dev (11/16/2011)
You cannot restore the backup of a compressed database to an uncompressed database.I didn't know this one. Can you please clarify what you mean with "compressed database"? Page/Row compression in place?
Not sure on type of compression.
RESTORE (Transact-SQL): Remarks #1
November 17, 2011 at 7:15 am
Dev (11/17/2011)
Gianluca Sartori (11/17/2011)
Dev (11/16/2011)
You cannot restore the backup of a compressed database to an uncompressed database.I didn't know this one. Can you please clarify what you mean with "compressed database"? Page/Row compression in place?
Not sure on type of compression.
RESTORE (Transact-SQL): Remarks #1
Yes, I've seen that one. But it doesn't make sense to me.
-- Gianluca Sartori
November 17, 2011 at 7:17 am
Silly question: is it a copy-only backup?
Not sure of the reason behind, but I've had several issues with copy-only backups in the past.
-- Gianluca Sartori
November 17, 2011 at 8:01 am
Gianluca Sartori (11/17/2011)
Silly question: is it a copy-only backup?Not sure of the reason behind, but I've had several issues with copy-only backups in the past.
No, straight backup. I think that GilaMonster was onto something with file corruption occurring when copied across the network. I am doing a manual copy of the backups to another server to see if that backup works versus the robocopy.
We do use the Maintenance Plans for backup and there is no way I see to turn of Checksum since it creates its own tsql. I am going to shift my full backups to Tues night since most of the corporation does full backups on Sunday and diffs on every other night.
November 18, 2011 at 1:06 pm
Under option, make sure the "Verify backup when finished" is checked. At lease that will tell if the backup finished sucessfully and your backup data is good.
Thanks,
Fitsum
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply