BACK UP - 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)

BACK UP

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-11-05 : 08:34:02

Mala writes "Hi,

I am trying to take a backup with the following commands.

1] BACKUP DATABASE BOS_2004_GP TO DISK = 'd:\bos_20004_gp.bak' WITH INIT
This statement works and generates a backup file.

2] BACKUP DATABASE BOS_2004_GP TO DISK = '\\mitmala\d\bos_20004_gp.bak' WITH INIT
The above statement gives the following error.
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\mitmala\d\bos_20004_gp.bak'. 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.

Can you please let me know what could be wrong. In the above case the database 'BOS_2004_GP', which I am trying to backup exists on the server 'MITMALA', the name which I specified in the second backup statement.

If this works, I want to take the backup across the network. "

zFudge
Starting Member

12 Posts

Posted - 2004-11-05 : 08:39:47

Sql can't backup on unc paths, u can map a drive with

DECLARE @connectdrive VARCHAR(60)
DECLARE @disconnectdrive VARCHAR(60)
SELECT @connectdrive = '\\net use p: \\mitmala\d'
EXEC Master..xp_cmdShell @connectdrive

BACKUP DATABASE BOS_2004_GP TO DISK = 'd:\bos_20004_gp.bak' WITH INIT

SELECT @disconnectdrive = '\\net use p: \\mitmala\d /d'
EXEC Master..xp_cmdShell @disconnectdrive

or map it in windows
This script maps the drive to p and disconnect is after its done.

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 08:45:49

"Sql can't backup on unc paths"

Shouldn't be a problem, but SQL Admin user (or whatever) will nee file permissions to the UNC

Kristen

zFudge
Starting Member

12 Posts

Posted - 2004-11-05 : 08:47:43

The Enterprise manager blocks it to. I tried to save to the local server.

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 11:42:55

BOL says

"If using a network server with a Uniform Naming Convention (UNC) name or using a redirected drive letter, specify a device type of disk."

So I reckon it shouldn't be a problem, permissions aside. Don't know about EM though

Kristen

Mala
Starting Member

26 Posts

Posted - 2004-11-06 : 00:15:06

Hi,

Thank you very much. With the mapping as said above, I can take the backups. But surprisingly it won't work if I specify the share name of the drive.

Mala

Mala
Starting Member

26 Posts

Posted - 2004-11-06 : 05:38:37

Hi,

Mapping & taking backup works fine for local network. But we have two server at different premises (different DB’s) which we are connecting through lease line. (not centralized server).
So when I try to take the back with above method, it doesn’t give any error. But dumping the backup to local drive . I tried setting the connection on both server name and IP address. Still it is storing the backup of local drive.

Please let me know is it possible to backup the database to local system from remote server.

Mala

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-08 : 03:27:11

quote:
Originally posted by Mala

Please let me know is it possible to backup the database to local system from remote server.

Mala

yes, it is possible and "do able". how are you connecting

quote:

BACKUP DATABASE BOS_2004_GP TO DISK = '\\mitmala\d\bos_20004_gp.bak' WITH INIT
The above statement gives the following error.
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\mitmala\d\bos_20004_gp.bak'. 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

you need to open the connection between the machines using net use
step 1:issue this: net use \\mitmala\d user:username\password
step 2: issue your backup command

--------------------
keeping it simple...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-08 : 03:46:48

quote:
Originally posted by Mala

Please let me know is it possible to backup the database to local system from remote server.

Mala

yes, it is possible and "do able". how are you connecting

quote:

BACKUP DATABASE BOS_2004_GP TO DISK = '\\mitmala\d\bos_20004_gp.bak' WITH INIT
The above statement gives the following error.
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\mitmala\d\bos_20004_gp.bak'. 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

Mala
Starting Member

26 Posts

Posted - 2004-11-08 : 05:08:03

Hi,

I am connecting to remote server as
SELECT @connectdrive = '\\net use p: \\mitmala\d'
EXEC Master..xp_cmdShell @connectdrive
(as the example give above)
I tried connecting it with both server name and IP address. For connection and disconnection it is not giving any error in both the case. But Back up it is storing on 'D' drive of same machine.

Are you telling me to connect to reomote server with username and password ? It that SQL user (say 'sa' and passwd OR is it NT user )

Regards,
Mala

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-08 : 05:13:25

quote:
Originally posted by Mala

Hi,

I am connecting to remote server as
SELECT @connectdrive = '\\net use p: \\mitmala\d'
EXEC Master..xp_cmdShell @connectdrive
(as the example give above)
I tried connecting it with both server name and IP address. For connection and disconnection it is not giving any error in both the case. But Back up it is storing on 'D' drive of same machine.

Are you telling me to connect to reomote server with username and password ? It that SQL user (say 'sa' and passwd OR is it NT user )

Regards,
Mala

NT user.what's the p: for? also if it's drive d then use d$ instead for default share or share a folder and apply appropriate read/write to the nt user.

simple test for connection: issue the net use command from source server to destination server, if you can explore the drive and write onto it, then permissions are set, you just need to do this once, but repeat everytime when either server is restarted.

--------------------
keeping it simple...

Mala
Starting Member

26 Posts

Posted - 2004-11-08 : 06:31:35

Hi,

I tried connecting and taking backup as below.
DECLARE @connectdrive VARCHAR(60)
DECLARE @disconnectdrive VARCHAR(60)
SELECT @connectdrive = '\\net use \\mitds\d user:mit\tim'
EXEC Master..xp_cmdShell @connectdrive

BACKUP DATABASE BOS_2004_GP TO DISK = 'd:\bos_20004.bak' WITH INIT

SELECT @disconnectdrive = '\\net use \\mitds\d /d'
EXEC Master..xp_cmdShell @disconnectdrive

Still it storing the backup on local drive.

For connection and disconnection statement it is giving the messgae
"output The filename, directory name, or volume label syntax is incorrect.
NULL"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-08 : 06:43:10

quote:
Originally posted by Mala

Hi,

I tried connecting and taking backup as below.
DECLARE @connectdrive VARCHAR(60)
DECLARE @disconnectdrive VARCHAR(60)
SELECT @connectdrive = '\\net use \\mitds\d user:mit\tim'
EXEC Master..xp_cmdShell @connectdrive

BACKUP DATABASE BOS_2004_GP TO DISK = '\\mitds\d\bos_20004.bak' WITH INIT

SELECT @disconnectdrive = '\\net use \\mitds\d /d'
EXEC Master..xp_cmdShell @disconnectdrive

Still it storing the backup on local drive.

For connection and disconnection statement it is giving the messgae
"output The filename, directory name, or volume label syntax is incorrect.
NULL"

you're backing up on the local drive, you need to specify the full path. also,

SELECT @connectdrive = 'net use \\mitds\d user:mit/tim'

did you try the simple test?

--------------------
keeping it simple...

Mala
Starting Member

26 Posts

Posted - 2004-11-08 : 06:49:42

Hi,

DECLARE @connectdrive VARCHAR(60)
SELECT @connectdrive = 'net use \\mitds\d user:mit/tim'
EXEC Master..xp_cmdShell @connectdrive

For this I am getting error as

System error 67 has occurred.

NULL
The network name cannot be found.

NULL
NULL

'MITDS' is our server.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-08 : 06:51:56

quote:
Originally posted by Mala

Hi,

DECLARE @connectdrive VARCHAR(60)
SELECT @connectdrive = 'net use \\mitds\d user:mit/tim'
EXEC Master..xp_cmdShell @connectdrive

For this I am getting error as

System error 67 has occurred.

NULL
The network name cannot be found.

NULL
NULL

'MITDS' is our server.

how about d? please try the simple test i mentioned before. you don't need to use sql for that since you will execute it on dos command line. if you fail in this first step, logically, backup will fail too.

--------------------
keeping it simple...

Mala
Starting Member

26 Posts

Posted - 2004-11-08 : 07:01:20

Hi,
From Dos command line I tried executing
1] Net use \\mitds\d user:mit/tim
"Systax Error 1362 has occured. Unkown user or bad password"
But the user name and password is correct .
2] Net send mitds "Hi"
Deliveres the message !

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-08 : 07:12:08

quote:
Sql can't backup on unc paths

It most certainly can:

BACKUP DATABASE BOS_2004_GP TO DISK = '\\mitmala\d$\bos_20004_gp.bak' WITH INIT

The only reason that wouldn't work are network permissions. You not only don't need a mapped drive, it's not recommended either. Don't run the backup from Enterprise Manager either, it's much easier to use Query Analyzer.

If you need to schedule a backup job you can put the BACKUP command in a regular T-SQL job step, no Maintenance plan necessary.

Mala
Starting Member

26 Posts

Posted - 2004-11-08 : 07:41:31

Hi,

1] BACKUP DATABASE BOS_2004_GP TO DISK = '\\mitmala\d$\bos_20004_gp.bak' WITH INIT

This works
2] BACKUP DATABASE BOS_2004_GP TO DISK = '\\mitds\e$\DB\bos_20004_gp.bak' WITH INIT

Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\mitds\e$\DB\bos_20004_gp.bak'. 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 have made sure I got full permission on 'E' drive of Mitds

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-08 : 07:43:15

how about folder db?

--------------------
keeping it simple...

Mala
Starting Member

26 Posts

Posted - 2004-11-08 : 07:46:58

Hi,

I created that folder on that drive after getting/setting full control permission on 'E' drive.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-08 : 07:51:34

If the SQL Server service is running under the LocalSystem account, it will not have network permissions to access that path. You need to change the login to a domain account that has the proper permissions.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources