Backing up to Network Location

Is there a way to get SQL Server to backup to a file on the network? I have tried running the SQL Server Agent under login with suitable access and using a UNC path. The SQL Server 2000 Maintenance Plan Wizard only allows you to browse to local drives which seems to indicate that this is true. The SQL 2005 Maintenance Plan looks more flexible but I haven't explored that possibility yet.

I solve the problem with a bit of VBScipt that copies the files and cleans up any old files on the local drive and on the network but I am curious as to whether there is another way. One reason for not alowing this could be to maximise the chance of the drive being available I suppose.

Thanks,

Dick Campbell

If you're running an Agent job to do the backup, SQL Agent has to have permissions on the network share. If you're executing a backup statement then the SQL Server process has to have permissions on the share and you have to be logged in as an admin.

Not sure about the Maintenance Plan Wizard behavior.

|||

Thanks, I thought that I had tried using an appropriate Agent login and network share. I will try again.

|||

Now I am really confused. I tried the SQL Server Agent running under my login (with rights to the network) and I copied and pasted the UNC for the network location from Windows Explorer into the backup path. When I do this, the Job fails. My alternative approach, which is a VBScript Step in a Job that copies the backup files to exactly the same network location fails with the SQL Server Agent running under the local system account but succeeds if I run it under my login.

Thanks for your replies so far.

|||If the Sql server startup account is local system the job will fail because with local system you cannot access the network resources .............the sql server startup account needs to be a ID which has access to network resources
And I guess since your id has has the necessary privileges to the network path you would have executed the backup command in the query analyzer and it would hav e succeeded i guess...........Always if you run it as a job you will need the startup account to be an id other than local system.............
|||

I got it working today by running the SQL Server Service under a login with network access rather than just the SQL Server Agent Service. I am not yet whether BOTH services need such a login.

It seems that SQL Server is set up to discourage direct backups over the network and I am not sure whether this id by design.

Any comments on the pros and cons of baking up directly to a network location?