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

CopyTo file missing

Randy DoubRandy Doub Posts: 26
edited September 4, 2012 2:10PM in SQL Backup Previous Versions
Anyone having issues in with their CopyTo files?
I have a large DB that gets a Full weekly and Diff daily. I used to backup directly to my network share but started having issues with "another process might be locking this file" messages. So I changed to backup locally with the CopyTo option. But it seems that sometimes my file is not in the CopyTo folder. No errors or warnings and the file is in the local folder.
I have about 20 other servers running SQL Backup and all use CopyTo and all work correctly. This one is the only place I use INIT. All other servers use EraseFiles=23h and EraseFiles_Remote=30.

I only keep seven days of backups online for this DB, and overwrite the daily Full/Diff file. Am I doing something wrong with my new backup command?

Here's the log file with the command in it. This is the Sunday Full backup. This .sqb was in the local backup folder, but not on the share when I looked this morning. Four of seven days of .sqb files were in the local folder and not on the share when I looked this morning. No warning or error emails.

SQL Backup log file

-SQL "BACKUP DATABASE [My_Database] TO DISK = 'f:\sql_backup\<DATETIME ddd>_<TYPE>_<DATABASE>.sqb' WITH COPYTO = '\\MyShare\sqlback01\My_Database\', COMPRESSION = 3, INIT, FILEOPTIONS = 4, MAILTO_ONERROR = 'me@myhouse.org' "


8/26/2012 10:15:00 AM: Backing up My_Database (full database) to:
8/26/2012 10:15:00 AM: f:\sql_backup\Sun_FULL_My_Database.sqb

8/26/2012 10:15:00 AM: BACKUP DATABASE [My_Database] TO VIRTUAL_DEVICE = 'SQLBACKUP_F829DF6C-7F60-48F6-BF3F-B01C8413733C' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (My_Database), 8/26/2012 10:15:00 AM', DESCRIPTION
= N'Backup on 8/26/2012 10:15:00 AM Server: SQLCNC006 Database: My_Database', FORMAT

8/26/2012 10:53:18 AM: Database size : 142.683 GB
8/26/2012 10:53:18 AM: Compressed data size: 20.069 GB
8/26/2012 10:53:18 AM: Compression rate : 85.93%

8/26/2012 10:53:18 AM: Processed 18665320 pages for database 'My_Database', file 'My_Database_Data' on file 1.
8/26/2012 10:53:18 AM: Processed 1 pages for database 'My_Database', file 'My_Database_Log' on file 1.
8/26/2012 10:53:18 AM: BACKUP DATABASE successfully processed 18665321 pages in 2295.008 seconds (63.539 MB/sec).
8/26/2012 11:05:43 AM: Copied f:\sql_backup\Sun_FULL_My_Database.sqb to \\MyShare\sqlback01\My_Database\Sun_FULL_My_Database.sqb.
8/26/2012 11:05:43 AM: SQL Backup process ended.


  • Options
    peteypetey Posts: 2,358 New member
    Looking at the duration taken to copy the file over to the network share (~13 minutes), is this normal? SQL Backup reports the outcome of the copying process depending on the result returned by the operating system, in this case a successful copy.

    Is it possible that another process is deleting the copied files on the network share? Could you install a directory monitoring app to log all activities on that folder, or use Windows auditing to monitor activities on the folder?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Yes ~13 minutes is normal for the 20gb file. I suspect the issues is with the replication of the the COPYTO folder. The COPYTO folder is a DFS share that is replicated offsite. I think the SysAdmins had/have issues with the replication. I'm monitoring, but I believe the problem is with us.
Sign In or Register to comment.