Error Backing up database to network path

MikeShepardMikeShepard Posts: 12
edited August 1, 2005 3:53AM in SQL Backup Previous Versions
When I try to backup certain databases to a network share, a 1K file gets written, and the log shows the following error.

Write on 'SQLBACKUP_108068141' failed, status = 112. See the SQL Server error log for more details.

Error 112 usually means out of space, but the drive in question has a lot of space left. Also, since a file is written (with the correct filename, even), there isn't a permissions issue. When I change the sp call to write the backup to a local drive, it works fine. This is happening on more than one server, but several servers don't have the problem. All servers are Win2k3 with SQL Server 2000 SP3a.

Any ideas?

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Mike,

    Can you please post the script or the command that you had run to make this error happen? In particular, I wonder if you're using the VERIFY option...
  • set @strSQL='-SQL "BACKUP DATABASE '+ @strName +' TO DISK = '''+@strPath+@strName+'\'+ @strName +'_sqlbackup'+replace(replace(convert(varchar(50),getdate() ,120),' ','-'),':','-')+'.sqb'' WITH NAME = ''Database '+ @strName +', '+replace(replace(convert(varchar(50),getdate() ,120),' ','-'),':','-')+''', DESCRIPTION = ''Backup on '+ replace(replace(convert(varchar(50),getdate() ,120),' ','-'),':','-')+' Database: '+ @servername +''', ERASEFILES = ' +cast(@intDays as varchar(2)) +', COMPRESSION = 1" -E'

    the error we see in the log is:
    SQL Backup 3.1.0, (c) Red Gate Software Ltd 2004 - 2005
    Serial number: [REDACTED]
    Backing up EPM88PRD (full database) to \\mmofile\sqlbackups\MMOPSEPM\EPM88PRD\EPM88PRD_sqlbackup2005-04-27-08-37-42.sqb ...
    Thread 0 error:
    Error 620: Error writing to backup file(s).
    Process terminated unexpectedly. Error code: -2139684860
    Msg 3202, Level 16, State 1, Server MMOPSEPM, Line 1
    Write on 'SQLBACKUP_201252733' failed, status = 112. See the SQL Server error log for more details.
    Msg 3013, Level 16, State 1, Server MMOPSEPM, Line 1
    BACKUP DATABASE is terminating abnormally.
    output

    From the SQL Server logs:
    BACKUP failed to complete the command BACKUP DATABASE [EPM88PRD] TO VIRTUAL_DEVICE = 'SQLBACKUP_201252733' WITH BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = 'Database EPM88PRD, 2005-04-27-08-37-42', DESCRIPTION = 'Backup on 2005-04-27-08-37-42 Database: EPM88PRD Server: MMOPSEPM', F

    BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device 'SQLBACKUP_201252733'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).

    Database backed up: Database: UTIL, creation date(time): 2005/04/04(15:30:08), pages dumped: 184, first LSN: 6:280:1, last LSN: 6:282:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_201256829'}).

    BackupMedium::ReportIoError: write failure on backup device 'SQLBACKUP_201207677'. Operating system error 112(There is not enough space on the disk.).

    The backup file is approximately 10G, and there is almost 90G free on the network share.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Mike,

    Can you try it with the -E parameter taken off of the end? -E only works for the command-line version of SQL Backup (sqlbackupc.exe) to tell it to use Integrated Windows Authentication. It has no effect in a SQL script, and I'm beginning to think there are some strange quirks when putting the -E switch in a SQL batch.
  • peteypetey Posts: 2,358 New member
    What is the file system of the network share (FAT32 or NTFS)?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • peteypetey Posts: 2,358 New member
    Could you pls also check the SQL Backup log file, that is usually found in C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log? The reason is because warning messages are logged there. Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 5/3/2005 3:18:29 PM: SQL Backup 3.1.0, (c) Red Gate Software Ltd 2004 - 2005
    5/3/2005 3:18:29 PM:
    5/3/2005 3:18:29 PM: Backing up EPM88PRD (full database) to \\mmofile\\sqlbackups\MMOPSEPM\EPM88PRD\EPM88PRD_sqlbackup2005-05-03-15-18-29.sqb ...
    5/3/2005 3:18:29 PM:
    5/3/2005 3:18:29 PM: BACKUP DATABASE [EPM88PRD] TO DISK = '\\mmofile\\sqlbackups\MMOPSEPM\EPM88PRD\EPM88PRD_sqlbackup2005-05-03-15-18-29.sqb' WITH NAME = 'Database EPM88PRD, 2005-05-03-15-18-29', DESCRIPTION = 'Backup on 2005-05-03-15-18-29 Database: EPM88PRD Server: MMOPSEPM', ERASEFILES = 3, COMPRESSION = 1
    5/3/2005 3:18:29 PM:
    5/3/2005 3:18:29 PM:
    5/3/2005 3:18:30 PM: Thread 0 error:
    Error 620: Error writing to backup file(s).
    Process terminated unexpectedly. Error code: -2139684860
    Thread 0 warning:
    Warning 610: Error writing to backup file: \\mmofile\\sqlbackups\MMOPSEPM\EPM88PRD\EPM88PRD_sqlbackup2005-05-03-15-18-29.sqb

    Msg 3202, Level 16, State 1, Server MMOPSEPM, Line 1
    Write on 'SQLBACKUP_8453389' failed, status = 112. See the SQL Server error log for more details.
    Msg 3013, Level 16, State 1, Server MMOPSEPM, Line 1
    BACKUP DATABASE is terminating abnormally.
  • That was a typo. I fixed the parameter and got the same error message (except that the path was correct)
  • peteypetey Posts: 2,358 New member
    After some help from Mike, turned out that on some servers, there was something blocking file writes larger than 1 MB to remote shares.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Can someone post the results of this :?: I've got a case where I cannot use file splitting to a UNC path. Works locally fine. This is with the wizard right now.

    I've found nothing on Google or I don't have the right keywords.

    Thanks in advance!
  • peteypetey Posts: 2,358 New member
    Have you checked your rights to write to the network share? When using the GUI, you need to ensure that the logged on user has write rights to the network share. This is different from running the standard SQL Server backup, where you need to ensure that the SQL Server service start-up user has adequate rights to the network share.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Yes, the permissions are correct. The process works when I do not use file splitting. Also the same path is being used by the Service Account I use that is SQL Server + Agent. I've logged in with it as well to test it, still same errors.

    The issue seems to be with file splitting, which would give be better performance, for now I'm suffering some, backup times are double what they should be, but still better than native SQL backup.
  • peteypetey Posts: 2,358 New member
    Could you pls post the error message or the contents of the log file? The log is found in C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log. Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Here is the log file when I try to split, at the bottom is when I do a single file:
    7/25/2005 3:41:55 PM: SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005 
    7/25/2005 3:41:55 PM: 
    7/25/2005 3:41:55 PM: Backing up imagenow_09 (full database) to \\sql2\c$\temp\FULL_SQL2_imagenow_09_20050725 154155_1.sqb, \\sql2\c$\temp\FULL_SQL2_imagenow_09_20050725 154155_2.sqb ...
    7/25/2005 3:41:55 PM: 
    7/25/2005 3:41:55 PM: BACKUP DATABASE [imagenow_09]  TO DISK = '\\sql2\c$\temp\FULL_SQL2_imagenow_09_20050725 154155_1.sqb', DISK = '\\sql2\c$\temp\FULL_SQL2_imagenow_09_20050725 154155_2.sqb' WITH NAME = 'db (imagenow_09)', DESCRIPTION = 'Backup of Database: imagenow_09  Instance: (local)  Server: SQL2', INIT, ERASEFILES = 7, COMPRESSION = 2
    7/25/2005 3:41:55 PM: 
    7/25/2005 3:41:55 PM: osql -S SQL2 -i "C:\DOCUME~1\sa-sql\LOCALS~1\Temp\sqbi410507251541599.txt" -b -w 120 -U sa -P XXXXXXXXXX
    7/25/2005 3:42:01 PM: Thread 1 error: 
    Error 620: Error writing to backup file(s).
    Thread 1 warning: 
    Warning 610: Error writing to backup file:  \\sql2\c$\temp\FULL_SQL2_imagenow_09_20050725 154155_2.sqb
    Warning: System error code:  (Insufficient system resources exist to complete the requested service)
    7/25/2005 3:42:01 PM: Backup data size    : 20.375 MB
    7/25/2005 3:42:01 PM: Compressed data size: 3.796 MB
    7/25/2005 3:42:01 PM: Compression rate    : 81.37%
    
    Processed 1752 pages for database 'imagenow_09', file 'imagenow_09_data' on file 1.
    Processed 3288 pages for database 'imagenow_09', file 'imagenow_09_index' on file 1.
    Processed 1 pages for database 'imagenow_09', file 'imagenow_09_log' on file 1.
    BACKUP DATABASE successfully processed 5041 pages in 3.224 seconds (12.806 MB/sec).[/size]
    
    
    7/25/2005 3:35:35 PM: SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005 
    7/25/2005 3:35:35 PM: 
    7/25/2005 3:35:35 PM: Backing up imagenow_09 (full database) to \\sql2\c$\temp\FULL_SQL2_imagenow_09_20050725 153535.sqb ...
    7/25/2005 3:35:35 PM: 
    7/25/2005 3:35:35 PM: BACKUP DATABASE [imagenow_09]  TO DISK = '\\sql2\c$\temp\FULL_SQL2_imagenow_09_20050725 153535.sqb' WITH NAME = 'Database (imagenow_09)', DESCRIPTION = 'Backup of Database: imagenow_09  Instance: (local)  Server: SQL2', INIT, ERASEFILES = 7, COMPRESSION = 2
    7/25/2005 3:35:35 PM: 
    7/25/2005 3:35:35 PM: osql -S SQL2 -i "C:\DOCUME~1\sa-sql\LOCALS~1\Temp\sqbi350507251535971.txt" -b -w 120 -U sa -P XXXXXXXXXX
    7/25/2005 3:35:40 PM: Backup data size    : 40.063 MB
    7/25/2005 3:35:40 PM: Compressed data size: 7.525 MB
    7/25/2005 3:35:40 PM: Compression rate    : 81.22%
    7/25/2005 3:35:43 PM: Process completed successfully.
    7/25/2005 3:35:43 PM: 
    
    Processed 1752 pages for database 'imagenow_09', file 'imagenow_09_data' on file 1.
    Processed 3288 pages for database 'imagenow_09', file 'imagenow_09_index' on file 1.
    Processed 1 pages for database 'imagenow_09', file 'imagenow_09_log' on file 1.
    BACKUP DATABASE successfully processed 5041 pages in 2.844 seconds (14.517 MB/sec).
    (1 row affected)
    

    Thanks so much for all your help!! :)
  • peteypetey Posts: 2,358 New member
    When you perform a split file backup, do both files get created, or only one? And could you give me the size of each file, if both were created?

    Do you encounter the same problem if you perform a native SQL Server backup using split files?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Yes both files are created, one I'm guessing is exactly half the full backup, what it should be. The other is very small. Has some control characters when opened with notepad.

    FULL_SQL2_imagenow_09_20050725 154155_1.sqb
    3,980,288 bytes

    FULL_SQL2_imagenow_09_20050725 154155_2.sqb
    512 bytes
  • peteypetey Posts: 2,358 New member
    Seems to be a variation of the problem faced by Mike. In his case, he could not write anything larger the 1 MB to a network path, while in your case, you can't write large blocks to a secondary file.

    Could you pls tell me what happens when you do the following:
    - create a split backup to another remote server
    - create a split backup to a UNC path, but this path points back to your own server

    Also, do you use Norton AntiVirus Corporate Edition on your server/workstation?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Update:
    I am able to do splits to the same locations as before if I use the Command-line version. :D (Really not the solution I am looking for...)

    Answers to your questions:
    My previous tests using the Stored Procedure, I was using UNC paths to the server itself, as well as other locations, neither worked for splitting. For example I have a server named SQL2.

    Split files to c:\temp worked, Split files to \\sql2\c$\temp do not. I also tried creating different shares other than admin shares, etc. This does not change the end result. Same thing with the remote server, not using file splitting let the backup complete correctly.

    This server and also the remote server do not have AV.
  • peteypetey Posts: 2,358 New member
    Any entries in the Windows event logs? And is this running on a 64-bit platform?
    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.