Warning 140 CopyTo File already exist

Running RedGate 7. We are backing up locally then copying the backup to off box folder and we get (not always) the following message:

12/3/2012 3:00:02 AM: Warning 140: COPYTO error: Destination file already exists. Will not overwrite: \\Company.com\public\SQLBackups\vm-prod06\RedGate\FULL_(local)_model_20121203_030000.sqb

This file really is there and everything is okay, but our senior dba doesn't want to put in the " /fileoption=4 " because that would mean that we will have to change a lot of code.

Basically why is this happening and how to prevent it (maybe there is a patch to red gate) Running Red Gate 7

Any help is appreciated,

Thank you, :?
~Keith

Comments

  • peteypetey Posts: 2,358 New member
    Could you please post the contents of a SQL Backup log for a backup that encountered this warning? The default folder where the logs are stored is C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name> on Windows 2003 and older, and C:\ProgramData\Red Gate\SQL Backup\Log\<instance name> on Windows Vista and newer.

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

    -SQL "BACKUP DATABASES [model,msdb,master]
    TO DISK = 'P:\backups\<database>\<AUTO>.sqb' WITH
    COPYTO = '\\hagerty.com\public\SQLBackups\vm-hdcsqlprod06\RedGate',
    ERASEFILES = 4, ERASEFILES_REMOTE = 7,
    MAILTO_ONERROR = 'db_admin@hagerty.com', DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10,
    COMPRESSION = 3, THREADCOUNT = 3 "

    PROCESSES COMPLETED SUCCESSFULLY

    11/29/2012 3:00:00 AM: Backing up model (full database) to:
    11/29/2012 3:00:00 AM: P:\backups\model\FULL_(local)_model_20121129_030000.sqb

    11/29/2012 3:00:01 AM: BACKUP DATABASE [model] TO VIRTUAL_DEVICE = 'SQLBACKUP_D71EF1A4-B314-4E61-8836-7F009E812573', VIRTUAL_DEVICE = 'SQLBACKUP_D71EF1A4-B314-4E61-8836-7F009E81257301', VIRTUAL_DEVICE = 'SQLBACKUP_D71EF1A4-B314-4E61-8836-7F009E81257302' WITH BUFFERCOUNT = 18, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (model), 11/29/2012 3:00:00 AM', DESCRIPTION = N'Backup on 11/29/2012 3:00:00 AM Server: VM-HDCSQLPROD06 Database: model', FORMAT

    11/29/2012 3:00:01 AM: Database size : 4.000 MB
    11/29/2012 3:00:01 AM: Compressed data size: 153.000 KB
    11/29/2012 3:00:01 AM: Compression rate : 96.26%

    11/29/2012 3:00:01 AM: Processed 160 pages for database 'model', file 'modeldev' on file 1.
    11/29/2012 3:00:01 AM: Processed 1 pages for database 'model', file 'modellog' on file 1.
    11/29/2012 3:00:01 AM: BACKUP DATABASE successfully processed 161 pages in 0.158 seconds (7.960 MB/sec).
    11/29/2012 3:00:02 AM: Deleting old backup file: P:\backups\model\FULL_(local)_model_20121124_030000.sqb
    11/29/2012 3:00:02 AM: Deleting old backup file: P:\backups\model\FULL_(local)_model_20121125_030000.sqb
    11/29/2012 3:00:02 AM: Warning 140: COPYTO error: Destination file already exists. Will not overwrite: \\hagerty.com\public\SQLBackups\vm-hdcsqlprod06\RedGate\FULL_(local)_model_20121129_030000.sqb

    11/29/2012 3:00:02 AM: Backing up msdb (full database) to:
    11/29/2012 3:00:02 AM: P:\backups\msdb\FULL_(local)_msdb_20121129_030002.sqb

    11/29/2012 3:00:02 AM: BACKUP DATABASE [msdb] TO VIRTUAL_DEVICE = 'SQLBACKUP_C6A0FEB9-46D6-481E-B051-B47EFFCC6592', VIRTUAL_DEVICE = 'SQLBACKUP_C6A0FEB9-46D6-481E-B051-B47EFFCC659201', VIRTUAL_DEVICE = 'SQLBACKUP_C6A0FEB9-46D6-481E-B051-B47EFFCC659202' WITH BUFFERCOUNT = 18, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (msdb), 11/29/2012 3:00:02 AM', DESCRIPTION = N'Backup on 11/29/2012 3:00:02 AM Server: VM-HDCSQLPROD06 Database: msdb', FORMAT

    11/29/2012 3:00:04 AM: Database size : 306.125 MB
    11/29/2012 3:00:04 AM: Compressed data size: 9.973 MB
    11/29/2012 3:00:04 AM: Compression rate : 96.74%

    11/29/2012 3:00:04 AM: Processed 12768 pages for database 'msdb', file 'MSDBData' on file 1.
    11/29/2012 3:00:04 AM: Processed 19 pages for database 'msdb', file 'MSDBLog' on file 1.
    11/29/2012 3:00:04 AM: BACKUP DATABASE successfully processed 12787 pages in 1.196 seconds (83.521 MB/sec).
    11/29/2012 3:00:05 AM: Deleting old backup file: P:\backups\msdb\FULL_(local)_msdb_20121125_030001.sqb
    11/29/2012 3:00:09 AM: Copied P:\backups\msdb\FULL_(local)_msdb_20121129_030002.sqb to \\hagerty.com\public\SQLBackups\vm-hdcsqlprod06\RedGate\FULL_(local)_msdb_20121129_030002.sqb.

    11/29/2012 3:00:09 AM: Backing up master (full database) to:
    11/29/2012 3:00:09 AM: P:\backups\master\FULL_(local)_master_20121129_030009.sqb

    11/29/2012 3:00:10 AM: BACKUP DATABASE [master] TO VIRTUAL_DEVICE = 'SQLBACKUP_0D081183-7EE8-4B29-BE77-13566338D65B', VIRTUAL_DEVICE = 'SQLBACKUP_0D081183-7EE8-4B29-BE77-13566338D65B01', VIRTUAL_DEVICE = 'SQLBACKUP_0D081183-7EE8-4B29-BE77-13566338D65B02' WITH BUFFERCOUNT = 18, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (master), 11/29/2012 3:00:09 AM', DESCRIPTION = N'Backup on 11/29/2012 3:00:09 AM Server: VM-HDCSQLPROD06 Database: master', FORMAT

    11/29/2012 3:00:10 AM: Database size : 5.500 MB
    11/29/2012 3:00:10 AM: Compressed data size: 559.500 KB
    11/29/2012 3:00:10 AM: Compression rate : 90.07%

    11/29/2012 3:00:10 AM: Processed 416 pages for database 'master', file 'master' on file 1.
    11/29/2012 3:00:10 AM: Processed 2 pages for database 'master', file 'mastlog' on file 1.
    11/29/2012 3:00:10 AM: BACKUP DATABASE successfully processed 418 pages in 0.197 seconds (16.571 MB/sec).
    11/29/2012 3:00:10 AM: Deleting old backup file: P:\backups\master\FULL_(local)_master_20121125_030004.sqb
    11/29/2012 3:00:10 AM: Deleting old backup file: \\hagerty.com\public\SQLBackups\vm-hdcsqlprod06\RedGate\FULL_(local)_master_20121122_030004.sqb
    11/29/2012 3:00:11 AM: Copied P:\backups\master\FULL_(local)_master_20121129_030009.sqb to \\hagerty.com\public\SQLBackups\vm-hdcsqlprod06\RedGate\FULL_(local)_master_20121129_030009.sqb.

    11/29/2012 3:00:17 AM: Mail sent successfully to: db_admin@hagerty.com
    ~Keith
  • thanks Peter for looking into this error.

    ~Keith
    ~Keith
  • Hi Peter,

    Just a note: Today, I ran the job and it didn't return a warning, I tried using the parameter "FILEOPTIONS = 4" it too didn't return a warning.

    very weird,

    Thanks again,
    ~Keith
  • peteypetey Posts: 2,358 New member
    Could you please run the following on the SQL Server instance that you are taking the backups on
    SELECT * FROM msdb..backupset WHERE database_name = 'model'
      AND backup_start_date &gt;= '2012-12-03 03:00' AND backup_finish_date &lt;= '2012-12-03 03:05'
    
    and post the results. Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi petey,

    Here is the results (hope it helps, but it shows nothing):

    backup_set_id backup_set_uuid media_set_id first_family_number first_media_number last_family_number last_media_number catalog_family_number catalog_media_number position expiration_date software_vendor_id name description user_name software_major_version software_minor_version software_build_version time_zone mtf_minor_version first_lsn last_lsn checkpoint_lsn database_backup_lsn database_creation_date backup_start_date backup_finish_date type sort_order code_page compatibility_level database_version backup_size database_name server_name machine_name flags unicode_locale unicode_compare_style collation_name is_password_protected recovery_model has_bulk_logged_data is_snapshot is_readonly is_single_user has_backup_checksums is_damaged begins_log_chain has_incomplete_metadata is_force_offline is_copy_only first_recovery_fork_guid last_recovery_fork_guid fork_point_lsn database_guid family_guid differential_base_lsn differential_base_guid compressed_backup_size


























    ----
































    (0 row(s) affected)
    ~Keith
  • peteypetey Posts: 2,358 New member
    Strange, it should at least have a record of the backup made of the 'model' database made on that date. Is your instance set up to delete backup history details older than 2 days?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi Peter,

    My mistake. here's the info:

    backup_set_id backup_set_uuid media_set_id first_family_number first_media_number last_family_number last_media_number catalog_family_number catalog_media_number position expiration_date software_vendor_id name description user_name software_major_version software_minor_version software_build_version time_zone mtf_minor_version first_lsn last_lsn checkpoint_lsn database_backup_lsn database_creation_date backup_start_date backup_finish_date type sort_order code_page compatibility_level database_version backup_size database_name server_name machine_name flags unicode_locale unicode_compare_style collation_name is_password_protected recovery_model has_bulk_logged_data is_snapshot is_readonly is_single_user has_backup_checksums is_damaged begins_log_chain has_incomplete_metadata is_force_offline is_copy_only first_recovery_fork_guid last_recovery_fork_guid fork_point_lsn database_guid family_guid differential_base_lsn differential_base_guid compressed_backup_size


























    ----































    1899 4C38B831-DBF6-45C7-BD01-540D813D6F88 1899 1 1 3 1 1 1 1 NULL 4608 Database (model), 12/1/2012 3:00:01 AM Backup on 12/1/2012 3:00:01 AM Server: VM-HDCSQLPROD06 Database: model ATG\sqlagent 10 50 2500 -20 0 27000000043100037 27000000044700001 27000000043100037 27000000040900037 2003-04-08 09:13:36.000 2012-12-01 03:00:01.000 2012-12-01 03:00:01.000 D 52 0 100 661 3705856 model VM-HDCSQLPROD06 VM-HDCSQLPROD06 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 NULL EBD0FE66-F91F-43F1-99C3-B52D803BB136 EBD0FE66-F91F-43F1-99C3-B52D803BB136 NULL NULL 3705856
    1902 D7AE1DAB-AD8C-4380-B21F-BAD70DE45477 1902 1 1 1 1 1 1 1 NULL 4608 model_backup_2012_12_01_030002_0986891 NULL ATG\sqlagent 10 50 2500 -20 0 27000000045300037 27000000046900001 27000000045300037 27000000043100037 2003-04-08 09:13:36.000 2012-12-01 03:00:26.000 2012-12-01 03:00:35.000 D 52 0 100 661 1396736 model VM-HDCSQLPROD06 VM-HDCSQLPROD06 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 NULL EBD0FE66-F91F-43F1-99C3-B52D803BB136 EBD0FE66-F91F-43F1-99C3-B52D803BB136 NULL NULL 1396736
    1942 D2C70A39-1DCA-4BB6-BA0B-D3FD7891C7AD 1942 1 1 3 1 1 1 1 NULL 4608 Database (model), 12/2/2012 3:00:00 AM Backup on 12/2/2012 3:00:00 AM Server: VM-HDCSQLPROD06 Database: model ATG\sqlagent 10 50 2500 -20 0 27000000047500037 27000000049100001 27000000047500037 27000000045300037 2003-04-08 09:13:36.000 2012-12-02 03:00:01.000 2012-12-02 03:00:01.000 D 52 0 100 661 3705856 model VM-HDCSQLPROD06 VM-HDCSQLPROD06 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 NULL EBD0FE66-F91F-43F1-99C3-B52D803BB136 EBD0FE66-F91F-43F1-99C3-B52D803BB136 NULL NULL 3705856
    1945 1846DC42-3741-4CFC-9B41-F5B923F1F148 1945 1 1 1 1 1 1 1 NULL 4608 model_backup_2012_12_02_030002_0026109 NULL ATG\sqlagent 10 50 2500 -20 0 27000000049700037 28000000001700001 27000000049700037 27000000047500037 2003-04-08 09:13:36.000 2012-12-02 03:00:06.000 2012-12-02 03:00:06.000 D 52 0 100 661 1463296 model VM-HDCSQLPROD06 VM-HDCSQLPROD06 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 NULL EBD0FE66-F91F-43F1-99C3-B52D803BB136 EBD0FE66-F91F-43F1-99C3-B52D803BB136 NULL NULL 1463296
    1985 CC7315AC-1EE3-4928-A6DE-2C1356550026 1985 1 1 3 1 2 1 1 NULL 4608 Database (model), 12/3/2012 3:00:00 AM Backup on 12/3/2012 3:00:00 AM Server: VM-HDCSQLPROD06 Database: model ATG\sqlagent 10 50 2500 -20 0 28000000002300037 28000000003900001 28000000002300037 27000000049700037 2003-04-08 09:13:36.000 2012-12-03 03:00:00.000 2012-12-03 03:00:01.000 D 52 0 100 661 3705856 model VM-HDCSQLPROD06 VM-HDCSQLPROD06 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 NULL EBD0FE66-F91F-43F1-99C3-B52D803BB136 EBD0FE66-F91F-43F1-99C3-B52D803BB136 NULL NULL 3705856
    1988 D930D6C2-EF1A-47F5-8611-0820F679DDE8 1988 1 1 1 1 1 1 1 NULL 4608 model_backup_2012_12_03_030002_2245378 NULL ATG\sqlagent 10 50 2500 -20 0 28000000004500037 28000000006100001 28000000004500037 28000000002300037 2003-04-08 09:13:36.000 2012-12-03 03:00:19.000 2012-12-03 03:00:24.000 D 52 0 100 661 1396736 model VM-HDCSQLPROD06 VM-HDCSQLPROD06 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 NULL EBD0FE66-F91F-43F1-99C3-B52D803BB136 EBD0FE66-F91F-43F1-99C3-B52D803BB136 NULL NULL 1396736
    2028 EFEC36FB-E122-48F9-B055-A480A9B314F6 2028 1 1 3 1 1 1 1 NULL 4608 Database (model), 12/4/2012 3:00:01 AM Backup on 12/4/2012 3:00:01 AM Server: VM-HDCSQLPROD06 Database: model ATG\sqlagent 10 50 2500 -20 0 28000000006700037 28000000008300001 28000000006700037 28000000004500037 2003-04-08 09:13:36.000 2012-12-04 03:00:01.000 2012-12-04 03:00:01.000 D 52 0 100 661 3705856 model VM-HDCSQLPROD06 VM-HDCSQLPROD06 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 NULL EBD0FE66-F91F-43F1-99C3-B52D803BB136 EBD0FE66-F91F-43F1-99C3-B52D803BB136 NULL NULL 3705856
    2031 6F180032-98EC-4858-B554-D501C718B83C 2031 1 1 1 1 1 1 1 NULL 4608 model_backup_2012_12_04_030002_7434868 NULL ATG\sqlagent 10 50 2500 -20 0 28000000008900037 28000000010500001 28000000008900037 28000000006700037 2003-04-08 09:13:36.000 2012-12-04 03:00:13.000 2012-12-04 03:00:17.000 D 52 0 100 661 1396736 model VM-HDCSQLPROD06 VM-HDCSQLPROD06 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 90D16058-F1D1-4E96-8EC6-8D7BFDA42F15 NULL EBD0FE66-F91F-43F1-99C3-B52D803BB136 EBD0FE66-F91F-43F1-99C3-B52D803BB136 NULL NULL 1396736

    (8 row(s) affected)
    ~Keith
  • peteypetey Posts: 2,358 New member
    The results show that only one backup was performed by SQL Backup each day, though there seemed to be another native backup performed by SQL Server after each SQL Backup backup.

    At this point, I don't know how the backup file got copied to the network share, before SQL Backup attempted to copy it over. We used to have a tool named CopyTool, that was a standalone application to copy backup files outside of SQL Backup. Do you happen to be using this tool, or another third-party app (Robocopy, Syncback etc), to copy the backup files?
    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.