Corrupt Copied TLog Backups

RichardB · August 22, 2006 12:20PM

Hi
I've had a shufti around and can't see anything about this.

I am logshipping beween 2 servers, using the copyto switch.

However occasionally (well 3 times today) the destination file has brought the restores to a halt due to corrupt file. Trying to restore the headeronly on the logship destination failed, trying to convert to trn failed.

Solution was simply to copy the file over again, and restart the restore job.

Running 4.5.704 on both machines, server2k3 and 2000Ent sp4.

Any help gratefully received!

petey · August 22, 2006 12:36PM

Is it possible that the network connection went down during the copy process?

RichardB · August 22, 2006 12:44PM

Hi.

No there were no logged network interruptions at all. That was my first thought. We have pretty tight logging of that kind of thing.

petey · August 22, 2006 1:23PM

Are the unusable copied files the same size as the original files, are they consistently the same size e.g. 1 KB, or are they just some random size?

RichardB · August 23, 2006 5:29AM

Good question, I shall have a look.
What I do remember is that they have a different modified date on them. The first copied set are 2 minutes ahead of the original files.

Other than that I shall have to double check.

RichardB · August 23, 2006 12:32PM

hi,
I have found a pair.
New works, Orig does not. By work I mean restore, or convert to sql server file.

MD5s:
Orig: 0FAF7B6268A7626ED5FC59C9B995D7AE
New: F0C3F24AEFF4B40F91D1A8AF4D2B6CCA

File size is reported the same.

petey · August 23, 2006 12:53PM

I'm a little confused now. Do you mean that you were able to restore using the copied file (new), but not from the original backup file (orig)?

Would you be able to send me copies of both the files?

RichardB · August 24, 2006 5:31AM

Ah sorry, no. The 'Orig' is the original copied file that failed and broke the logshipping. 'New' is the replacement copied file which was used to restart the logship. Apologies for the confusion with the poor semantics!

Both are supposed to be copies of the same file on the source server.

petey · August 24, 2006 5:38AM

Can you compare the 2 files to determine where the differences lie, and if there is a pattern to it e.g. data is offset by x bytes, or the last x bytes are zeros etc? Thanks.

RichardB · August 24, 2006 5:41AM

Daft question but what do I look at it with..?

:oops:

RichardB · August 24, 2006 5:52AM

Ok I had a look with a hex editor, the Orig (corrupt) is filled with 0 from just over halfway through, the Second one(good) has data throughout.

The content prior to that looks the same.

petey · August 25, 2006 4:46AM

Could you pls check the SQL Backup log for that backup, if a warning was raised for the failed copy? Thanks.

RichardB · August 25, 2006 5:33AM

Ah. Indeed a failure.

Processed 2860 pages for database ***, file ***_Log' on file 1.
BACKUP LOG successfully processed 2860 pages in 0.394 seconds (59.447 MB/sec).
22/08/2006 12:26:02: Warning 141: COPYTO error: Unable to copy U:\LogShippingBackup\LOG_MSSQL_***_20060822_122503.sqb to \\***\LogShippingLanding\LOG_MSSQL_***_20060822_122503.sqb.

*** edited

The servers are seperated by a vpn. What is also of concern is that the vpn went over this morning for a few minutes, resulting in no copy of a log file. The rest then went on their merry way when service resumed.

Is there an option to retry the copy every 10 minutes or so if it fails?

Also this copy failure (and this mornings) did not show in the failure section of the GUI, only in the logs.

Would it be a fair assumption to say that the corrupt files I am experiencing are likely to be a glitch in the vpn - ie the disk space for the file is reserved, partly populated and then the connection goes awol for a moment resulting in a half empty file?

petey · August 25, 2006 1:31PM

No, there isn't an option to retry the copy.

The copy failure raises a warning. An error is raised only when a backup fails. The backup job should have been flagged as failed by SQL Server Agent if the standard script was used.

SQL Backup copies files using the CopyFile Windows API command (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/fs/copyfile.asp). The process is as you described: the file is generated on the target location, with the size fully allocated and zero-filled before the actual copy begins.

RichardB · August 30, 2006 11:49AM

Ok thanks.

I have stuck in a conditional xcopy as the next step. This seems to overcome the issue.

Anyone else bothered by this feel free to pm me.

astreet · April 25, 2007 9:43AM

Hi,

Any plans to add a retry option to your product if the file copyto fails to copy the file to the specified directory?

petey · April 25, 2007 11:23AM

In version 5, 5 attempts are made to copy the file over to the destination folder, in intervals of 1 second after each failed attempt.

Gladiatr · September 26, 2008 5:08PM

RichardB wrote:

Hi
I've had a shufti around and can't see anything about this.

I am logshipping beween 2 servers, using the copyto switch.

However occasionally (well 3 times today) the destination file has brought the restores to a halt due to corrupt file. Trying to restore the headeronly on the logship destination failed, trying to convert to trn failed.

Solution was simply to copy the file over again, and restart the restore job.

Running 4.5.704 on both machines, server2k3 and 2000Ent sp4.

Any help gratefully received!

Hi,

I am having this similar kind of a problem with my log shipping machine and can I get some input on this please.

The details are below:

We have a production server (PS) and a standby server (LS).

On the PS and LS we are running SQLBackup 4.6 and every night we take the full backup of our production databases on the PS and mirror them to the LS using the following command:

exec master..sqlbackup N'-SQL "BACKUP DATABASE [DatabaseName] TO DISK = [E:\SQLbackups\DatabaseName_<DATETIME yyyymmdd_hhnnss>] WITH NAME = [<AUTO>], DESCRIPTION = [<AUTO>], PASSWORD = ''<ENCRYPTEDPASSWORD>somepassword</ENCRYPTEDPASSWORD>'', KEYSIZE = 256, COMPRESSION = 3, THREADS = 1, INIT, MIRRORFILE=[\\LS2\SQLBackup\DatabaseName_<DATETIME yyyymmdd_hhnnss>], ERASEFILES=7"', @exitcode OUTPUT, @sqlerrorcode OUTPUT

Once the full backup is completed then we backup the transaction logs every 10 minutes on the PS and mirror it to LS using the following command:

exec master..sqlbackup N'-sql "BACKUP LOG [DatabaseName] TO DISK = ''E:\SQLbackups\Transaction Logs\DatabaseName_Log_<DATETIME yyyymmdd_hhnnss>.sqb'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', PASSWORD = ''<ENCRYPTEDPASSWORD>somepassword</ENCRYPTEDPASSWORD>'', KEYSIZE = 256, MIRRORFILE = ''\\LS2\SQLBackup\DatabaseName_Log_<DATETIME yyyymmdd_hhnnss>.sqb'', COMPRESSION = 3, THREADS = 1"', @exitcode OUTPUT, @sqlerrorcode OUTPUT

and then the LS runs a scheduled job to restore the transaction logs backup by executing the command below:

exec master..sqlbackup N'-sql "RESTORE LOG [DatabaseName] FROM DISK = ''E:\SQLBackup\DatabaseName_Log_*.sqb'' WITH STANDBY = ''E:\DATABASES\UNDO\UNDO_DatabaseName.DAT'', PASSWORD = ''somepassword'', MOVETO = ''E:\SQLBackup\DatabaseNameTransactionLogArchive\''"', @exitcode OUTPUT, @sqlerrorcode OUTPUT

This has been happening for the past two years or so until one day I checked my logs on the LS and noticed that they are sitting there since July and have not been restored by the restore job above.

On further investigation I noticed that we have a drive space issue on the PS when the transaction job was executing actually on the PS. The backup log file size on the PS is 0 locally. But if you notice the transaction backup job mention above that is executing on the PS it is suppose to mirror the same file to the LS as well. So I went to see if I can find the file on the LS and I got the file with the 17KB in size. When I tried to restore the file manually it giving me the following error:

Not a SQL Backup nor native backup file: 'E:\SQLBackup\DatabaseName_Log_20080727_032000.sqb'
SQL Backup exit code: 600

I ran the verify command on the transaction log file and it says:

Verifying file:
E:\SQLBackup\DatabaseName_Log_20080727_032000.sqb

Error 570: File is not a SQL Backup file.
Error 715: Backup file is not encrypted.

SQL Backup exit code: 715

I checked the next transaction file and it gave the the same error, then I checked couple of more and they are ok and nothing wrong with them.

I also used the SQB2MTF command-line utility and the GUI utility but this time it's saying the password is incorrect. I used both the utilities with the good transaction log and with the same password and it worked like a charm.

So is there a way we can fix these corrupted transaction log files so that I can use them to restore on my LS without again having to restore a full backup of the database and start from the scratch?

I will appreciate your help in this regards.

Thanks
gladiatr.

Corrupt Copied TLog Backups

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk