Solved

Backup issue - MS SQL server 2005

Posted on 2013-01-08
4
1,070 Views
Last Modified: 2013-02-11
Hi All,

We are taking Full & Diff backups through SQL Agent (Jobs are scheduled).

All servers backups are running fine. But the Full & Diff backup jobs are failing for 2 servers (SERVER_INST01, SERVER_INST02).

We are not able to find root cause of the issue. Please guide/share suggestions on our backup issue.

Please find the attached diff backup TSQL script & error details given below.

Executing the query "BACKUP DATABASE [ReportServerInt] TO  DISK = N'\\\\XYZ.DOMAIN.COM\\MYCOMP\\PROD\\SERVER_INST01\\Diffrential\\ReportServerInt\\ReportServerInt_backup_201301071900.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'ReportServerInt_backup_20130107190048', SKIP, REWIND, NOUNLOAD,  STATS = 10
" failed with the following error: "Write on "\\\\XYZ.DOMAIN.COM\\MYCOMP\\PROD\\SERVER_INST01\\Diffrential\\ReportServerInt\\ReportServerInt_backup_201301071900.bak" failed: 64(The specified network name is no longer available.)
A nonrecoverable I/O error occurred on file "\\\\XYZ.DOMAIN.COM\\MYCOMP\\PROD\\SERVER_INST01\\Diffrential\\ReportServerInt\\ReportServerInt_backup_201301071900.bak:" 64(The specified network name is no longer available.).
BACKUP DATABASE is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Executing the query "BACKUP DATABASE [USER_DB1] TO  DISK = N'\\\\XYZ.DOMAIN.COM\\MYCOMP\\PROD\\SERVER_INST02\\Diffrential\\USER_DB1\\USER_DB1_backup_201301071900.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'USER_DB1_backup_20130107190048', SKIP, REWIND, NOUNLOAD,  STATS = 10
" failed with the following error: "Write on "\\\\XYZ.DOMAIN.COM\\MYCOMP\\PROD\\SERVER_INST02\\Diffrential\\USER_DB1\\USER_DB1_backup_201301071900.bak" failed: 121(The semaphore timeout period has expired.)
A nonrecoverable I/O error occurred on file "\\\\XYZ.DOMAIN.COM\\MYCOMP\\PROD\\SERVER_INST02\\Diffrential\\USER_DB1\\USER_DB1_backup_201301071900.bak:" 64(The specified network name is no longer available.).
BACKUP DATABASE is terminating abnormally.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
            0

Message
Executed as user: DOMAIN\USER1. ...9.00.5000.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    
Started:  7:00:00 PM  Progress: 2013-01-07 19:00:47.69     Source: {57B2FA24-FD3C-498C-A0BE-D2EDB289FB72}      
Executing query "DECLARE @Guid UNIQUEIDENTIFIER      
EXECUTE msdb..sp".: 100% complete  End Progress  Progress: 2013-01-07 19:00:48.69    
Source: Back Up Database Task      
Executing query "EXECUTE master.dbo.xp_create_subdir N'\\XYZ.DOMAIN".: 7% complete  End Progress  Progress: 2013-01-07 19:00:48.74    
 Source: Back Up Database Task      
Executing query "EXECUTE master.dbo.xp_create_subdir N'\\XYZ.DOMAIN".: 14% complete  End Progress  Progress: 2013-01-07 19:00:48.79    
Source: Back Up Database Task      Executing query "EXECUTE master.dbo.xp_create_subdir N'\\XYZ.DOMAIN".: 21% complete  
End Progress  Progress: 2013-01-07 19:00:48.85     Source: Back Up Database Task      Executing query "EXECUTE master.dbo.xp_create...  
The package execution fa...  The step failed.

Thanks in Advance.
Diff-backup-script.txt
0
Comment
Question by:sg05121983
4 Comments
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 83 total points
ID: 38753983
your  XYZ server maybe is too busy - check there memory \cpu (you may need more) , nic you may need faster and you may need firmware upgrade there -- . maybe fasted disks ; check RAID config there. etc.. maybe change backup times  - too free the XYZ server resources a bit

I would recommend  to find alternative destination for backup until you take care XYZ server..
0
 
LVL 9

Assisted Solution

by:MattSQL
MattSQL earned 83 total points
ID: 38758179
0
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 84 total points
ID: 38758528
Please make sure path is available and It should be no longer than 255 characters.

Note: Write permission should be there for SQL Agent user on Folder.
0
 

Author Closing Comment

by:sg05121983
ID: 38879206
--
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now