Error while trying to backup a database

[joozh]

This has been a funny week. things that were running just fine till last week (and for the last many months) have started giving problems despite that I have not changed any settings.

The problem is that one of my scheduled jobs has started failing. The job used to simply takle a backup of a database. When I tried to probe into it and tried to manually take the backup (via Enterprise Manager..) I get a dialog box with the message:

Cannot open backup device 'D:\Backup\Sales'. Device error or device off-line.See the SQL server error log for more details.
Backup database is terminating abnormally.


Can someone kindly guide me how to solve this.

Many TIA.

[MAK]

Whats in the error log?

[joozh]

Originally posted by MAK
Whats in the error log?

I hope I am looking in the correct place to answer your question. I am doing a copy/paste of what appear to be 2 relevant entires in the SQL server logs:


BackupDiskFile::CreateMedia: Backup device 'D:\Backup\Sales' failed to create. Operating system error = 5(Access is denied.).


and the 2nd copy/paste is:

BACKUP failed to complete the command BACKUP DATABASE [Sales] TO DISK = N'D:\Backup\Sales' WITH INIT , NOUNLOAD , NAME = N'Sales backup', NOSKIP , STATS = 10, NOFORMAT DECLARE @i INT
select @i = position from msdb..backupset where database_name='Sales'and type!='F' and backup_set_id=(s

Does this provide any clue?

The funny thing is that the same account is scheduled to backup another database in the same folder and that's running just fine

I also tried to manually backup the database to another location and no problem but then why the original problem??? I am really

[Rawhide]

Check the size of the database. Do you have enough space to create the backup?

Are you providing the media password? If you use "Init" with "NoSkip", you have to provide a valid media password.

[chetanjain04]

Hi,

Please check whether SQL server has requisite permissions on the backup folder. The SQL server agent service account should have permissions on the backup folder for you to take the backup successfully.


regards,

chetan

[joozh]

HI Rawhide and chetanjain04,

Appreciate your replies. Thanks.

It turned out the the problem was caused becuase someone (having admin rights on our LAN), had created a sub-folder by the same name i.e.

If the backup was supposed to be placed in C:\Backup and the name of the file was Sales, someone had created a sub-folder by the name of Sales - C:\Backup\Sales and this was causing the problem.

I am posting this for the benefit of other members.

Again thanks for your replies and help

Regards.