SQL Server Performance

Error while trying to backup a database

Discussion in 'General DBA Questions' started by Joozh, Apr 19, 2005.

  1. Joozh New Member

    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:BackupSales'. 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. dineshasanka Moderator

    This occurs when the user account under which the MSSQLSERVER service is running does not have the appropriate permissions to access the network path.
    check this for more infomation
    http://www.quantumsoftware.com.au/SupportDownloads/KB/Article.aspx?ID=15

    quote:Originally posted by 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:BackupSales'. 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.
  3. Joozh New Member

    Thanks for the reply but a similar job is also scheduled to take the backup of another database and it is running with the same account and not giving any problems? So how come the job is failing for one account while another similar job is not failing despite the fact that the same account is used?<br /><br />Any clues? <img src='/community/emoticons/emotion-6.gif' alt=':(' />
  4. dineshasanka Moderator

    Did you check the Sql Server Log?<br />what it says?<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Joozh</i><br /><br />Thanks for the reply but a similar job is also scheduled to take the backup of another database and it is running with the same account and not giving any problems? So how come the job is failing for one account while another similar job is not failing despite the fact that the same account is used?<br /><br />Any clues? <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  5. satya Moderator

    Check whether that backup file is used by any other process, if you use any third party tools or tape backup.

    Try to backup the database to a different file to check the statement against this database is working. IF the OS is win 2003 then refer to this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;827452 for furthe information.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. Joozh New Member

    Thanks for a clue satya. <br /><br />Maybe the problem is becuase the backup is happening when the backup of data (on the tape) is taking place??? Maybe so... I'll try to change the schedule and see what happens.<br /><br /><br />dineshasanka, also thanks for the reply. Her's what the log from SQL server says:<br /><br />BackupDiskFile:<img src='/community/emoticons/emotion-3.gif' alt=':O' />penMedia: Backup device 'D:BackupSales' failed to open. Operating system error = 5(Access is denied.).<br /><br /><br />And a copy/paste of another related entry (from the job history):<br /><br />Executed as user: UKT1usr123. Cannot open backup device 'D:BackupSales'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated statement is not prepared [SQLSTATE HY007] (Error 0) Cannot open backup device 'D:BackupSales'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) VERIFY DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.<br /><br /><br />Does this help?
  7. dineshasanka Moderator

    Same error,<br /><a href='http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5315' target='_blank' title='http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5315'<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5315>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5315</a></a><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Joozh</i><br /><br /><br />BackupDiskFile:<img src='/community/emoticons/emotion-3.gif' alt=':O' />penMedia: Backup device 'D:BackupSales' failed to open. Operating system error = 5(Access is denied.).<br /><br />And a copy/paste of another related entry (from the job history):<br /><br />Executed as user: UKT1usr123. Cannot open backup device 'D:BackupSales'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Associated statement is not prepared [SQLSTATE HY007] (Error 0) Cannot open backup device 'D:BackupSales'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) VERIFY DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.<br /><br /><br />Does this help?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">

Share This Page