operation completed successfully, or did it?

philcart · May 21, 2007 2:56AM

Running SQL Backup 5.0.0.2770 on Windows Server 2003 Enterprise with SQL Server 2005 Enterprise.

After the backup I received the following errror,

Warning 141: COPYTO error: Unable to copy E:\Backups\FULL_SQL1_MyDB_20070521163726.sqb to
\\SQL2\LogShipping\db\FULL_SQL1_MyDB_20070521163726.sqb &#40;The operation completed successfully&#41;.

The file was not copied and the service accounts have full permissions to the share.

Chris Auckland · May 23, 2007 10:57PM

By the looks of the message, the backup was a success, but the copy to the network share failed. I suspect this happened because the network location was unavaliable for some reason at the time of the backup.

It could also be that the SQL Server doesn't have enough contiguous memory to use as buffer space for a backup over the network. Since SQL Server provides this memory space to SQL Backup, it's possible that on a heavily used server that has been up for a long time. The workaround is to manually specify the buffer size in the backup command. This parameter is called MAXDATABLOCK, and must be a multiple of 65536.

For instance, you could amend your backup command to read:

master..sqlbackup N'-SQL "BACKUP DATABASE [Database] TO DISK = ''\\backup-server\d$\FULL_Database.sqbâ€ WITH NAME = ''Database (Database)'', DESCRIPTION = '' '', INIT, MAILTO = ''pgrayson@tpp-uk.com'', COMPRESSION = 1, MAXDATABLOCK=65536"'

If this doesn't work, you can also add MAXTRANSFERSIZE, which is similar to MAXDATABLOCK, but affects memory usage for both local and network backups. MAXTRANSFERSIZE can be used in conjunction with MAXDATABLOCK for handling low-memory situations.

philcart · May 24, 2007 4:56AM

So does SQL Backup actually perform another backup when the COPYTO option is used?

We're already using the MAXTRANSFERSIZE = 65536 option.

The network share is available, it is constantly being used for transaction log backups as part of a seperate Log Shipping process.

Maybe in this instance the job should complete with a Warning, or some sort of failure.

Thanks
Phill

petey · May 27, 2007 10:39PM

SQL Backup does not create another backup when you use the COPYTO option. It just attempts to copy the file over to the specified folder(s).

In this case, it appears to be failing, raising warning code 141. The message 'The operation completed successfully' is wrong, as SQL Backup appears to have retrieved the wrong Windows error code.

Is the COPYTO option failing everytime, or just intermittently? Thanks.

philcart · May 28, 2007 1:34AM

The copy was failing due to lack of space on the target system.

Maybe a feature for the future is to provide options for SQL Backup to clean up the "COPYTO" directory as well as the local directory.

Thanks for your help.

petey · May 28, 2007 2:22AM

There is such an option to delete old backup files in the COPYTO folder(s). Use the FILEOPTIONS parameter, with the value 1 e.g.

EXEC master..sqlbackup '-sql "BACKUP DATABASE ... WITH COPYTO = ..., ERASEFILES = 7, FILEOPTIONS = 1" '

This will erase all backup files for the selected instance and database older than 7 days, in the primary backup folder(s) and in the COPYTO folder(s).

See the topic 'The BACKUP Command' in the help file, under 'The SQL Backup Toolkit\Toolkit Syntax' for more details.

philcart · May 28, 2007 5:56AM

Great, then how about a change to command then. Allow different file ages between primary and secondary servers.

EG: Only keep the last day on the primary but keep the last 5 days on secondary.

operation completed successfully, or did it?

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk