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

MIRRORFILE Option in SQLBackup 5.3

GladiatrGladiatr Posts: 28
edited October 24, 2008 5:06AM in SQL Backup Previous Versions
Hi All,

One thing I don't understand about the MIRRORFILE Option in SQLBackup 5.3 is that when you are using the SQLBackup backup command to get the backup locally along with the MIRRORFILE Option just in case if local backup fails it will create the MIRROR backup file to some other location. Some of my backups are failing due to some reasons locally and are creating 0KB size file locally. But, on the other hand using the MIRRORFILE option is also creating the backup of the same file with a proper backup size. But, when I run to verify the backup or try to restore from this backup created by MIRRORFILE option the SQLBackup throws upon me the following error:

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

This baffles me and happens only when the local backup fails and create 0 KB size file.

I appreciate your help.

Thanks.
-gladiatr

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Could you please send me the following:

    - the SQL Backup log file for the backup
    The default folder where the logs are stored is C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>.

    - any one of the backup file that's experiencing this problem, if it's feasible
    Can be any type of backup.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Hi Petey,

    Thanks for replying to my post.

    On the production server (PS) we have a job schedule to run every 10 minutes executing the following command to generate a transaction log file locally:

    DECLARE @exitcode int
    DECLARE @sqlerrorcode int

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

    IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
    BEGIN
    RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
    END

    Once this job executes, it creates a sqlbackup log file locally on the PS and then it mirrors the log file to the standby server (LS).

    The following log file generated when we had the space issue on the PS with the log file of zero size. Here is the log:

    SQL Backup log file
    10/11/2008 2:00:01 AM: Backing up Application (transaction log) to:
    E:\SQLbackups\Transaction Logs\Application_Log_20081011_020001.sqb
    \\LS2\Solomon\Application_Log_20081011_020001.sqb

    10/11/2008 2:00:01 AM: BACKUP LOG [Application] TO DISK = 'E:\SQLbackups\Transaction Logs\Application_Log_20081011_020001.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', PASSWORD = 'XXXXXXXXXX', KEYSIZE = 256, MIRRORFILE = '\\LS2\Solomon\Application_Log_20081011_020001.sqb', COMPRESSION = 3, THREADS = 1

    Thread 0 warning:
    Warning 210: Error writing to backup file: E:\SQLbackups\Transaction Logs\Application_Log_20081011_020001.sqb
    Warning: System error code: (There is not enough space on the disk)
    10/11/2008 2:00:06 AM: Backup data size : 49.500 MB

    Processed 5698 pages for database 'Application', file 'Application_Log' on file 1.
    BACKUP LOG successfully processed 5698 pages in 3.749 seconds (12.450 MB/sec).

    The following command restore the logs on the LS:

    DECLARE @exitcode int
    DECLARE @sqlerrorcode int

    exec master..sqlbackup N'-sql "RESTORE LOG [Application] FROM DISK = ''E:\Solomon\Application_Log_*.sqb'' WITH STANDBY = ''E:\DATABASES\UNDO\UNDO_Application.DAT'', PASSWORD = ''password'', MOVETO = ''E:\Solomon\ApplicationTransactionLogArchive\''"', @exitcode OUTPUT, @sqlerrorcode OUTPUT

    IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
    BEGIN
    RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
    END

    When I try to restore the file from the LS which was generated by the MIRRORFILE option produces the following log:

    SQL Backup log file
    10/11/2008 2:10:00 AM: Not a SQL Backup nor native backup file: 'E:\Solomon\Application_Log_20081011_020001.sqb'

    Since this happens, I have to always restore the full backup once again then apply the individual log files to make the log shipping working.

    Being in the following situation, this made me think that the MIRRORFILE option is no more reliable.

    This is just one database example I mentioned here. I have 18 other databases running on the PS and jobs similar to the one I mentioned above to generate sqlbackup logs and because of this space issue those jobs obviously created 0 size file but at the LS end the MIRRORFILE option looks like created a reliable restorable transaction log file but it fails only when you try to restore those logs on the LS.

    So the outcome, my log shipping breaks everytime the space issue arises.

    Your help is appreciated.

    Thanks again.
    -gladiatr.
  • Options
    peteypetey Posts: 2,358 New member
    Could you please tell me the version of the SQL Backup Agent service file (SQBCoreService.exe) that's installed? From the log, it doesn't look like its version 5.3.

    Also, could you please send me any one of the log files that cannot be restored? If it's large, send me an e-mail and I'll arrange an FTP account for you.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.