SQL Server Performance Forum – Threads Archive

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: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.

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.

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? Any clues? <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />

Did you check the Sql Server Log? what it says? <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Joozh 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? Any clues? <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> <hr height="1" noshade id="quote"></blockquote id="quote">

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.

Thanks for a clue satya. 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. dineshasanka, also thanks for the reply. Her’s what the log from SQL server says: 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.). And a copy/paste of another related entry (from the job history): 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. Does this help?

Same error, <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> <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Joozh 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.). And a copy/paste of another related entry (from the job history): 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. Does this help? <hr height="1" noshade id="quote"></blockquote id="quote">

]]>