COPYTO Option does not work for LOG file backup

tkastle · February 4, 2019 6:32PM

I have a SQL Server 2012 install and SQL Backup 9.2.x.

It has been installed for quite a few years and recently the COPYTO statement of the "master..sqlbackup" statement no longer creates the copy.

I have dozens of other backup for Full and Differential backups in which the COPYTO works just fine.

If I run the exact same command using the command line SQLBackupC.exe (with minor syntax changes) the COPYTO option works just fine.

This command runs and the COPYTO option works as expected (files are in both locations):
SQLBackupC.exe -I MSSQLSERVER -SQL "BACKUP LOG [CORP] TO DISK = 'D:\sansqldata\backup_db\<database>\tran\<AUTO>.sqb' WITH COPYTO = 'E:\TestBackup\DBs\<DATABASE>\tran'"

This command run from inside SQL Server does not work (ONLY the ..\sansqldata\backup_db\... file exists - nothing in the ..\TestBackup\... folder):
EXECUTE master..sqlbackup '-SQL "BACKUP LOG [CORP] TO DISK = ''D:\sansqldata\backup_db\<database>\tran\<AUTO>.sqb'' WITH COPYTO = ''E:\TestBackup\DBs\<DATABASE>\tran''"'

Does anyone have any suggestions on what else I can test?

Eddie D · February 6, 2019 11:58AM

Hi, thank you for your forum post.

When using the SQL Backup Extended Stored Procedure to perform your backup tasks, there is a difference in the COPYTO parameter actions between Full and Differential backup compared to log backups.

When performing a Full or Differential backup using the COPYTO parameter, the COPYTO process will copy the backup file, if successfully created, to the path provided as part of the backup job. The COPYTO process is recorded in the logging file created for the job.

For Transaction Log backups using the COPYTO command, the COPYTO process performs a different action. On completion of a successful Log backup, COPYTO process is NOT initiated. An entry is entered into a component called the Log Copy Queue and the Log backup job completes without copying the backup file created to the path specified. There is no information recorded in the logging file for the COPYTO process. The SQL Backup Agent service, monitors the Log Copy Queue and when a new entry is added, it copy's the Log backup file created to the path specified in the job syntax.

Further information on the Log Copy Queue can be found here. The Log Copy Queue simply provides a mechanism to retry the copy process if the copy fails. The retry period takes place over a 24 hour period.

If you wish for the COPYTO process to be identical to what occurs with Full and Differential backup jobs, add the USESIMPLECOPY keyword to your log backup syntax, as per the example below:

EXECUTE master..sqlbackup N'-SQL "BACKUP LOG [<Databasename>] TO DISK=''C:\Backups\<TYPE>_<DATABASE>_<DATETIME YYYYmmddhhnnss>.sqb''  WITH COPYTO =  "\\TargetServer1\Folder1",  USESIMPLECOPY, COMPRESSION = 3"'

tkastle · February 6, 2019 6:40PM

Eddie,

Thank you for the quick response. This did answer my question and created a new question in regards to the same COPYTO for log files.

As a note: The "new way" (without the USESIMPLECOPY) started writing to the remote folder after I submitted the initial question. I believe I restarted the SQL Backup Agent service and this somehow "jump-started" the Log Copy process.

I've tested now the same command with and without the USESIMPLECOPY and the Log backup files are being written to the remote folder. I like the immediate action of the USESIMPLECOPY, however I like that the Log Copy Queue will keep trying until (for the most part) the file is copied to the remote folder (in reading about the USESIMPLECOPY if the remote folder is not available that copy could fail...).

The "New Question" that comes up is that the ERASEFILES_REMOTE seems to not work on Log files and I don't see anything in either they BACKUP command documentation or the Log Copy Queue documentation that would tell me why.

I have also tried the ERASEFILES with ERASEFILES_REMOTE combination and the ERASEFILES_PRIMARY with ERASEFILES_SECONDARY and while the "local" files are cleaned up as I would expect nothing happens in either case with Log files on the remote folder.

I have similar backups for non-Log files with ERASEFILES and ERASEFILES_REMOTE and those work as I would expect. Just with Log files it does not work correctly. I think I may be missing something.

Thank you.

Eddie D · February 11, 2019 10:02AM

Hi, thank you for your reply.

There are no differences in erase files behavior between Full, Differential and Log backup.

I recommend that you use the ERASEFILES_PRIMARY and ERASEFILES_SECONDARY keywords, as in my experience they operate better than ERASEFILES and ERASEFILES_REMOTE.

The process checks the backup file header in all the files in the folder. If backup file matches the criteria on age or time, database name and backup type it will delete the file.

If you analyze the log file for the T-Log backup's, is there any attempt to delete backup files in the COPYTO location?

Many Thanks
Eddie

COPYTO Option does not work for LOG file backup

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk