Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 11th, 2006, 01:26 PM
Registered User
 
Join Date: Apr 2006
Location: Reston, VA, .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default backup to network location

I have server1 and server2. I'm trying to backup database of server2 to server1. This is what I wrote:

BACKUP DATABASE my_database TO DISK= '\\server1\DB_backup\my_database.bak'

The two servers maping each other. And I change both MSSQLSERVER log on account to administrator which can access both server.

But it shows me the error message:

Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\server1\DB_backup\my_database.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.

Anyone can tell me what should I do? Thanks a lot!!




 
Old April 11th, 2006, 08:08 PM
Kep Kep is offline
Authorized User
 
Join Date: Aug 2003
Location: Melbourne, VIC, Australia.
Posts: 79
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can't backup to network drives or shares. Your only option would be to run a backup to the local disk and then copy the file to the network share.

For the copy you have a few choices. A couple that spring to mind are:-

1. Call xp_cmdshell
Code:
EXEC xp_cmdshell 'COPY localbackup.bak \\myserver\sharename\remotebackup.bak'
There are a number of things to consider with this. Firstly, your SQL server service account will need to have network access. Usually this is not the case because it runs under the LocalSystem account. Secondly, by default the only people who can run xp_cmdshell are members of the sysadmin fixed server role.

2. Create a SQL Agent job that copies the file.

This is similar to the above except that you specify that the job step is an 'Operating System Command (CmdExec)'.

This command runs under the context of the SQL Server Agent, so that service would need to be running as an account with network access.

Then you would use:-
Code:
EXEC msdb..sp_start_job 'Copy job name'
I use this solution to move backups to a remote location, just in case something happens locally (like a fire in the server room).

My SQLAgent is also part of replication so it needs network access for that, therefore it runs under a domain account. We called it SQLAgent and we only allow it to log on as a service.

Another option is ActiveX scripts but you'll still end up with the issue of service accounts and network access.


Kep.
 
Old May 23rd, 2006, 12:14 PM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Came across this and felt I needed to correct the last comment, you can backup across the network, and it is a common task of any DBA with more then 6 months experience. Permissions come in to play.


Make sure the user account in which SQL is running has permissions to write the backup to the remote folder you’re trying to access. If the sql engine is running under the system account, give folder permissions to the machine account in the domain.

This should work just fine.




Similar Threads
Thread Thread Starter Forum Replies Last Post
Create backup folder & also backup files miracles Excel VBA 0 April 16th, 2007 02:28 AM
Backup scandalous Access 3 February 9th, 2007 01:19 PM
What's the best way to backup? thomasks Visual Basic 2005 Basics 2 September 13th, 2006 03:36 PM
How to backup database to Network Drive Jane SQL Server 2000 3 July 4th, 2005 12:37 PM
BAckUp sachindhillan Oracle 2 September 28th, 2003 07:32 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.