?
Solved

SQL DB backups to network drives

Posted on 2007-10-11
9
Medium Priority
?
372 Views
Last Modified: 2012-05-05
We are low on space on our SQL server and I would like to backup my databases to a network drive.
I did do some searches on this site, but what has been suggested I get errors. What I would like to do is to be able to create a maintenance plan that will allow me to back up to the network or is the only way to backup to the network is to use query analyzer?

I am running SQL 2000 with SP4

Thanks,
0
Comment
Question by:akbcomputers
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20061227
What about backing up to a local drive and one done, move the backup to the network drive?
I use MP's to create backups to SAN disks all the time ... I don't even have disks in my machines !!!

So it should work ... what errors are you encountering ?
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 20061357
1) You must set trace flag 1807 on.
2) You must specify the full UNC path name (\\servername\...) *not*, for example, just a mapped drive letter.

So, for example, try something like this:

DBCC TRACEON (1807)

BACKUP DATABASE model
TO DISK = '\\servername\full\path\to\backup\file\model.bak'
0
 
LVL 35

Expert Comment

by:David Todd
ID: 20062172
Hi,

For a previous company I had this working, but had to do the following:
In the script check for the drive mapping, and if necessary remap the drive.

Create a SQL start-up procedure that mapped the drive.

I got fairly good throughput over-night - 100GB in around an hour. The servers where on a Gigabit switch.

Regards
  David
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:akbcomputers
ID: 20062367
There is not enough space on the local machine to handle the DB.

The error I get is:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\servername\full\path\to\backup\file\model.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.


0
 
LVL 35

Expert Comment

by:David Todd
ID: 20062412
Hi,

Do try what I suggested about with mapping a drive and using that. Note that it does need to be mapped within SQL's process - hence the startup proc.

The command to map a drive is
net use f: \\someotherserver\someshare

Regards
  David
0
 

Author Comment

by:akbcomputers
ID: 20062531
I tried that, I get a syntax error 'f:'

I also tried master..xp_cmdshell 'net use f: \\someotherserver\someshare' which was working on another server, but not this server
0
 
LVL 18

Assisted Solution

by:Yveau
Yveau earned 500 total points
ID: 20063645
It looks like the SQL Server service account does not have permissions on that network resource.
In that case, you can't map the network location to a drive.

Hope this helps ...
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 20065548
You did replace this name:
 '\\servername\full\path\to\backup\file\model.bak'
with your actual remote computer name, path name, and backup name, right? :-)

Also, Yveau is of course correct: make sure the account doing the backup has full permissions on the remote server directory.
0
 

Author Comment

by:akbcomputers
ID: 20068491
Oh, I have to use my server name...

Sheesh :P

It was the account. One server is running under the local system account when the other is using an actual account. Didn't realize that.

Thanks Yveau and Scott as i still needed the query.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question