Solved

Backup issue - MS SQL server 2005

Posted on 2013-01-08
4
1,082 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

910 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

24 Experts available now in Live!

Get 1:1 Help Now