What are the challenges you face when working across database platforms? Take the survey
Options

Corrupt Copied TLog Backups

RichardBRichardB Posts: 35
edited September 26, 2008 5:08PM in SQL Backup Previous Versions
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!

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Is it possible that the network connection went down during the copy process?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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.
  • Options
    peteypetey Posts: 2,358 New member
    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?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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.
  • Options
    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.
  • Options
    peteypetey Posts: 2,358 New member
    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?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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.
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Daft question but what do I look at it with..?

    :oops:
  • Options
    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.
  • Options
    peteypetey Posts: 2,358 New member
    Could you pls check the SQL Backup log for that backup, if a warning was raised for the failed copy? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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?
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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.
  • Options
    Hi,

    Any plans to add a retry option to your product if the file copyto fails to copy the file to the specified directory?
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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.
Sign In or Register to comment.