SQL DB backups to network drives

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,
akbcomputersAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
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
 
YveauCommented:
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
 
David ToddSenior DBACommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
akbcomputersAuthor Commented:
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
 
David ToddSenior DBACommented:
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
 
akbcomputersAuthor Commented:
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
 
YveauCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
akbcomputersAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.