Strange Backup error

Question

Strange Backup error

Andrew Vasylevskyy

SSC Eights!

Points: 927
More actions
September 1, 2005 at 10:46 am

#110273

Hi All,
I am running SQL 2000 (SP3) on Windows 2003. I have set up a maintenance plan to backup databases to network disk. Backup for 4 databases works correctly but for other 2 databases I receive following error:
18204 : BackupDiskFile :: OpenMedia: Backup device '\\9.1.1.14\F$\Backups\ACC_db_200509010732.BAK' failed to open. Operating system error = 2(The system cannot find the file specified.).
When I run backup statement from Query Analyzer it works fine.
When I change path in maintenance plan from network to local path and run the job - works correctly.
I realy don't know how to backup solve this issue.
Thanks in advance.
Andriy

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Are all of the backups being made to the same location?

One thing to consider, when you run the backup from QA you are doing that with whatever permissions YOU have. When you run backups from a JOB (such as a Maintenance Plan), then the backup is using the login assigned for the SQLServerAgent Service. That login must have permission to access the location where the backup file will be written to.

-SQLBill

Andrew Vasylevskyy SSC Eights! Points: 927 More actions · Answer 2

All backups are being made to the same location.

I've created one maintenance plan for 6 databases. So, they are backuped within one job.

When I run the job it creates 4 backups successfully and fails for 2 others.

Login assigned for the SQLServerAgent Service has all required permissions to that network path. It’s proved by that it creates for 4 files successfully. Also login assigned to System Administrator server role.

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Are you backing up the 'backup file' to tape? Could something else be accessing the network backup file in between the job steps? Are you running an antivirus program on the file?

Have you checked the Windows Event Viewer logs on both machines?

-SQLBill

Henning Peter Jensen SSC Journeyman Points: 94 More actions · Answer 4

Check if the account running SQL Server and SQl Server Agent has write access to the specified location.

Andrew Vasylevskyy SSC Eights! Points: 927 More actions · Answer 5

SQL Server's & SQL Server Agent's accounts have Administrator's rights on both machines.

Andrew Vasylevskyy SSC Eights! Points: 927 More actions · Answer 6

I'm backuping to network machine on it's HDD.

Symantec antivirus is running on the destanation machine.

Event Log contains next errors :

3041 : BACKUP failed to complete the command BACKUP DATABASE [ACC] TO DISK = N'\\9.1.1.14\l$\Backups\ACC_db_200509020022.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

18204 : BackupDiskFile :: OpenMedia: Backup device '\\9.1.1.14\l$\Backups\ACC_db_200509020022.BAK' failed to open. Operating system error = 2(The system cannot find the file specified.).

jasonhatfield01 Right there with Babe Points: 722 More actions · Answer 7

Andrew - in your original post you have \\9.1.1.14\F$ and in your last update you have \\9.1.1.14\l$. Is this a typo or do you have both of those drive letters on the destination? Does the "Backups" folder exist on both paths?

Andrew Vasylevskyy SSC Eights! Points: 927 More actions · Answer 8

I've got both letters. I tried a lot of variants to solve this issue.

Of course all folders exist.

jasonhatfield01 Right there with Babe Points: 722 More actions · Answer 9

Have you tried creating individual maintenance plans for the two databases that fail?

The only other thing that I can think of would be a space issue where it doesn't have enough room to backup all six databases before deleting old copies?

homebrew01 SSC Guru Points: 55544 More actions · Answer 10

If Jason's right, and you don't have enough space, then you have to modify your job to delete your old backup first. I ran into this in one situation. SQL gives warning messages if you modify the job that the maint plan created, but I do it anyway. I had to leave the maint plan step 1 as step 1. Then I added a step 2 to to delete the old backup, but set step 2 to execute first, then go to step 1 to run the new backup. Keep in mind, that you are without a backup for the duration of the new backup, and if the new backup fails, you don't have your previous backup anymore.

Andrew Vasylevskyy SSC Eights! Points: 927 More actions · Answer 11

There is enough free space on destination disk.

I tried to create maintenance plan only for those 2 database but it failed with the same error

Joan OBryan Mr or Mrs. 500 Points: 510 More actions · Answer 12

Double check your spec. As jasonhatfield01 points out here, the two are different. That is not an upper case "I" - it must be a lower case "L". Is that really what you intended?

bluepaperbag SSCrazy Points: 2055 More actions · Answer 13

i get something similar to this. someone come up with an answer.

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 14

Does your Sql Server operating system sit on one disc and your databases sit on another? The only time I've had this error is when the physical disc is full i.e. although there was space in the location of the backup folder, there was no space in the location of the sql server or the location of the databases.

Madame Artois