Can't open backup device - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Can't open backup device

Author

Topic

Mamatha
Posting Yak Master

102 Posts

Posted - 2004-12-22 : 07:52:42

Hi

I want to restore SQL Server 2000 backup file using ASP.I have a code to restore but it gives errors like

"Microsoft SQL-DMO (ODBC SQLState: 42000) (0x800A0C81)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\backupi.BAK'. Device error or device off-line. See the SQL Server error log for more details. [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE is terminating abnormally.
/Intranet/ex3.asp, line 28"

The code is :

"<%
Set srv=Server.CreateObject("SQLDMO.SQLServer")
srv.Connect "server", "sa", "thanks"
Set rest=Server.CreateObject("SQLDMO.Restore")
rest.Action=0 ' full db restore
rest.Database="Arizona"
rest.Devices=Files
rest.Files ="\\server\D\Program Files\Microsoft SQL Server\MSSQL\BACKUP\backup1. BAK"
rest.ReplaceDatabase=True 'Force restore over existing database
rest.SQLRestore(srv)
response.write "Done!"
set rest=nothing
set srv=nothing
%>

What is the problem?why it gave errors like that..Please give me the solution.This is very urget need for me.

Many thanks in advance

Mamatha

Mamatha

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-22 : 15:04:28

Well, does this path work from the database server:

\\server\D\Program Files\Microsoft SQL Server\MSSQL\BACKUP\backup1. BAK

???

Must be a valid path from the database server as that's where the BACKUP command runs and not from your client machine.

Tara

Mamatha
Posting Yak Master

102 Posts

Posted - 2004-12-23 : 00:29:33

Hi tduggan

Thanks for your reply.I used path like "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\backup1.BAK" and executed on the server, but i got the same error.What is the problem?Please give me the solution.

Mamatha

Mamatha

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-23 : 02:15:57

The path doesn't exist or the sql server account doesn't have access to it.

try executing
master..xp_cmdshell 'dir \\server\D\"Program Files"\"Microsoft SQL Server"\MSSQL\BACKUP\backup1. BAK'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-23 : 12:12:31

Did you see if that is a valid path through Windows Explorer on the database server?

Tara

anurag0583
Starting Member

1 Post

Posted - 2008-12-03 : 03:22:38

Hi,

The solution is to place the file path between square brackets like "[C:\Program Files\Microsoft SQL Server\80\MSSQL\Backup\pubs.bak]" instead of using "C:\Program Files\Microsoft SQL Server\80\MSSQL\Backup\pubs.bak"

So, in your case, you should keep your file path as

"[\\server\D\Program Files\Microsoft SQL Server\MSSQL\BACKUP\backup1. BAK]"

This will work fine.

Regards,
Anurag Srivastava
[url]http://www.aprajita.com[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-03 : 15:18:33

Why do people provide answers to threads that are 4 years old!?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

migro
Starting Member

1 Post

Posted - 2009-08-13 : 19:30:38

Because other people might profit from the information?

Irwin
Starting Member

1 Post

Posted - 2010-01-06 : 09:38:00

Hi,
we use Tivoli for sql server backup. When I was trying to back up DBs from one of 3 instances (migrated from sql server 7.) I got such error:

ACO5422E Received the following from the MS SQL server:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device '.....'. Operating system error 0x80070005(error not found).
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally. Microsoft SQL-DMO (ODBC SQLState: 42000) (HRESULT:0x80040c81)

and I've find the next solution:

"...To work around the problem, use the TCP/IP Net-Library instead of the Named Pipes Net-Library to connect to the SQL Server database, and then back up or restore the database..."
The problem was solved

For more:
http://support.microsoft.com/kb/827452

sebantom1979
Starting Member

6 Posts

Posted - 2013-03-04 : 13:40:51

http://answerforu.com/2011/09/26/server-msg-3201-level-16-state-1-line-1-cannot-open-backup-device-epubs-dat-device-error-or-device-off-line-see-the-sql-server-error-log-for-more-details-server-msg-3013-level-16-stat/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources