backup across the network - 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

SQL Server 2000 Forums

SQL Server Administration (2000)

backup across the network

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-22 : 08:21:12

Bob writes "I am trying to backup a SQL database to another server. the syntax that I am using is; BACKUP DATABASE Northwind TO DISK = '\\SHQ-VFILE-02\IS\CORPSQL-BACKUPS\Test' WITH INIT.
I am getting the following error message;
'Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\SHQ-VFILE-02\IS\CORPSQL-BACKUPS\Test'.
Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.'
I get the same error when I try to run the backup from Enterprise Manager. The user that is used for the backup has all priveledges everywhere !
Can you help."

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-10-22 : 09:07:06

Try mapping a network drive to it and then try.

*************************
Someone done told you wrong!

srf
Starting Member

42 Posts

Posted - 2002-10-22 : 12:30:16

I would check that the account SQL server logs on with (not the account the SPID is logged on as) isn't "local system", and that the account being used has permissions to the network resource.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2002-10-22 : 12:35:45

The problem is that you haven't specified a file name. Try adding a file name to your command: BACKUP DATABASE Northwind TO DISK = '\\SHQ-VFILE-02\IS\CORPSQL-BACKUPS\Test\Northwind.bak' WITH INIT

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-22 : 14:23:46

This is most likely a permissions issue with the SQL Service Account
However verify the UNC path you have supplied is correct, try using
xp_cmdshell and the DIR cmd to list its contents

quote:

The problem is that you haven't specified a file name

Calling a database backup file xxx.bak is merely a convention used by EM, it has no effect on the ability to backup and restore to a file e.g.

 
use master
go
backup database pubs to disk='c:\foo.bar'
go
restore database pubs from disk='c:\foo.bar' with replace
go
backup database pubs to disk='c:\foobar'
go
restore database pubs from disk='c:\foobar' with replace
go

HTH
Jasper Smith

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2002-10-22 : 16:29:21

Go ahead and try running his command but point most of the UNC path to a network server that you have. You'll see that you get the same error as he does and that when you supply a file name, it works. It is not a permission problem.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2002-10-22 : 16:35:53

Let me be more clear.

It fails when you have a folder named Test (at least for his command). If you don't have a folder named Test, then it works fine. I don't think that he is trying to name the backup file Test, but rather backup the database in the Test folder.

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-10-22 : 16:41:02

Tduggan, I think your first theory is correct, about the file UNC path not containing a filename, mainly because if everything is installed correctly, you would never get this error in attempting the backup through Enterprise manager.

***************************************
Death must absolutely come to enemies of the code!

rkc01
Starting Member

43 Posts

Posted - 2002-10-22 : 19:15:29

The file name is part of the problem but it may still be a permissions issue. If you can't get it, I have a down and dirty solution that always works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources