Options
CopyTo file missing
Randy Doub
Posts: 26
Anyone having issues in 7.1.0.72 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 7.1.0.72 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 7.1.0.72
-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' "
PROCESSES COMPLETED SUCCESSFULLY
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.
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 7.1.0.72 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 7.1.0.72
-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' "
PROCESSES COMPLETED SUCCESSFULLY
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.
Comments
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?
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8