dcsimg
Results 1 to 6 of 6

Thread: Error while trying to backup a database

Hybrid View

  1. #1
    Join Date
    Dec 2003
    Posts
    140

    Error while trying to backup a database

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  3. #3
    Join Date
    Dec 2003
    Posts
    140
    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

  4. #4
    Join Date
    Feb 2003
    Posts
    1,048
    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.

  5. #5
    Join Date
    Mar 2005
    Location
    India
    Posts
    6
    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

  6. #6
    Join Date
    Dec 2003
    Posts
    140
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •