MIRRORFILE Option in SQLBackup 5.3
Gladiatr
Posts: 28
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
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
- 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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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.
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8