Error Backing up database to network path

MikeShepard · April 26, 2005 11:46AM

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?

Brian Donahue · April 27, 2005 5:59AM

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...

MikeShepard · April 27, 2005 9:42AM

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 Donahue · April 27, 2005 10:20AM

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.

petey · April 27, 2005 10:50AM

What is the file system of the network share (FAT32 or NTFS)?

petey · April 28, 2005 11:48PM

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.

MikeShepard · May 3, 2005 4:20PM

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.

MikeShepard · May 3, 2005 4:55PM

That was a typo. I fixed the parameter and got the same error message (except that the path was correct)

petey · May 9, 2005 12:43PM

After some help from Mike, turned out that on some servers, there was something blocking file writes larger than 1 MB to remote shares.

chrisflint · July 11, 2005 5:53PM

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!

petey · July 13, 2005 2:24AM

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.

chrisflint · July 25, 2005 2:49PM

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.

petey · July 25, 2005 10:01PM

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.

chrisflint · July 25, 2005 11:04PM

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, &#40;c&#41; Red Gate Software Ltd 2004 - 2005 
7/25/2005 3:41:55 PM: 
7/25/2005 3:41:55 PM: Backing up imagenow_09 &#40;full database&#41; 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 &#91;imagenow_09&#93;  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 &#40;imagenow_09&#41;', DESCRIPTION = 'Backup of Database: imagenow_09  Instance: &#40;local&#41;  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&#40;s&#41;.
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:  &#40;Insufficient system resources exist to complete the requested service&#41;
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 &#40;12.806 MB/sec&#41;.&#91;/size&#93;

7/25/2005 3:35:35 PM: SQL Backup 3.2.0, &#40;c&#41; Red Gate Software Ltd 2004 - 2005 
7/25/2005 3:35:35 PM: 
7/25/2005 3:35:35 PM: Backing up imagenow_09 &#40;full database&#41; 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 &#91;imagenow_09&#93;  TO DISK = '\\sql2\c$\temp\FULL_SQL2_imagenow_09_20050725 153535.sqb' WITH NAME = 'Database &#40;imagenow_09&#41;', DESCRIPTION = 'Backup of Database: imagenow_09  Instance: &#40;local&#41;  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 &#40;14.517 MB/sec&#41;.
&#40;1 row affected&#41;

Thanks so much for all your help!!

petey · July 25, 2005 11:18PM

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.

chrisflint · July 25, 2005 11:24PM

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

petey · July 26, 2005 12:42AM

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.

chrisflint · July 27, 2005 6:03PM

Update:
I am able to do splits to the same locations as before if I use the Command-line version.

(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.

petey · August 1, 2005 3:53AM

Any entries in the Windows event logs? And is this running on a 64-bit platform?

Error Backing up database to network path

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk