August 18, 2014 at 7:15 am
Hello --
We had a Full and subsequent Differential backup scheduled for this past weekend, and both failed. Both backups, along with the Transaction Log backups are part of a Maintenance Plan. A check of the details for both jobs within SQL Studio revealed the following text in each:
Code: 0xC002F210 Sour... The package execution fa... The step failed.
The Transaction Log backups are working without issue.
Can someone provide help on this issue?
Thanks.
August 18, 2014 at 7:19 am
Have you checked anything already, for example, is there enough space in the backup location to store the backups?
-------------------------------------------------
Trainee DBA
August 18, 2014 at 7:24 am
No where near enough info. That error message is useless
Try taking a backup from a query window (backup database ... to disk = '<backup location>') and post any and all errors.
Anything useful in the error log?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2014 at 9:05 am
Hello --
I checked the log files for the Full and Differential backups. The Full Backup logfile had the following entry:
Failed:(-1073548784) Executing the query "BACKUP DATABASE [RayStationPatientDB_Thoracic_4014..." failed with the following error: "Write on "T:\\BACKUP\\Full and Differential Backups\\RayStationPatientDB_Thoracic_4014_backup_2014_08_16_220002_8866239.bak" failed: 1167(The device is not connected.)
BACKUP DATABASE is terminating abnormally.
Processed 260312 pages for database 'RayStationPatientDB_Thoracic_4014', file 'RayStationPatientDB_4014' on file 1.
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.
The Differential Backup had the entry shown below:
Failed:(-1073548784) Executing the query "BACKUP DATABASE [RayStationPatientDB_Thoracic_4014..." failed with the following error: "Cannot open backup device 'T:\\BACKUP\\Full and Differential Backups\\RayStationPatientDB_Thoracic_4014_backup_2014_08_17_220002_7698330.bak'. Operating system error 55(The specified network resource or device is no longer available.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
There are a total of twenty databases of various sizes that being backed up. As far as I can tell all other databases were backed up successfully. The database in question is one the larger backups, and the size of the bak file is seventy-four gigabytes.
August 18, 2014 at 9:08 am
danrst171 (8/18/2014)
Have you checked anything already, for example, is there enough space in the backup location to store the backups?
have you checked the backup location for free space?
-------------------------------------------------
Trainee DBA
August 18, 2014 at 9:10 am
Hello --
Yes, the first thing that I did was verify there was enough space for the bak files.
August 18, 2014 at 9:14 am
The important message appears to be "The device is not connected". What is your T drive - a local drive, a SAN drive, a drive with mount points, removable media? How many times has the backup of this database failed? If it was only once, try re-running it. If it's more than once, did other backups succeed in between times?
John
August 18, 2014 at 9:46 am
The drive in question is connected to a SAN. The log file backups are being saved to the drive in question. I did further investigating, and there was a network issue over the weekend that may have been the cause of the problem.
A Differential backup is scheduled to run this evening, so I will monitor the job to see if the problem continues to occur.
August 18, 2014 at 9:49 am
kaplan71 (8/18/2014)
The drive in question is connected to a SAN. The log file backups are being saved to the drive in question. I did further investigating, and there was a network issue over the weekend that may have been the cause of the problem.A Differential backup is scheduled to run this evening, so I will monitor the job to see if the problem continues to occur.
Glad you checked that. The error message will usually mean there was a network hiccup or that the storage is unreachable for any reason.
Next thing though is that it appears you are performing these backups via Maintenance Plans. Is that valid?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2014 at 9:58 am
That is correct. All backups and a cleanup of older trn files are part of one Maintenance Plan.
The Differential backup of Friday night completed successfully. It was only the Full on Saturday, and Differential on Sunday nights that failed.
August 18, 2014 at 10:09 am
kaplan71 (8/18/2014)
That is correct. All backups and a cleanup of older trn files are part of one Maintenance Plan.The Differential backup of Friday night completed successfully. It was only the Full on Saturday, and Differential on Sunday nights that failed.
I have seen these types of hiccups from Maintenance plans as well. Maintenance plans are extremely fragile. Worse is that Maintenance plans take more resources and offer less manageability of the maintenance.
I'd recommend evolving off maintenance plans and institute script based versions for better performance, consistent results, less headache, and higher flexibility.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 19, 2014 at 6:49 am
Hello --
Last night's scheduled Differential backup completed successfully, and the Transaction Log backups are also completing without incident. It appears at this point the weekend failures were due to the network issues. That being said, I will continue to closely monitor the backups for the rest of the week, and will post any updates.
Thanks again for the help on this.
August 25, 2014 at 9:23 am
Hello --
The scheduled backups for this past weekend completed successfully, so I believe the problem from the previous weekend was due, at least in part, to the network issue mentioned in a previous posting.
Thank-you to all who responded.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply