SQL Server 2005 Express: back up to a folder on the network

Question

I am using this code as a procedure on an application. At the mid-night, it will trigger the procedure.

http://www.geekzilla.co.uk/View487F82A5-C96B-4660-A070-F7C8B7FC4431.htm

I changed this locally back-up:

TO  DISK = N''c:\db backup\'

to:

TO  DISK = N''\\nlbkserver02\backup_devicem01$\'

It is working normally on the local machine but it did not work on a network drive. The error details is as following:

Cannot open backup device
'\\nlbkserver02\backup_devicem01$\15-Dec-2010
- application 1'. Operating system error 5(error not found). BACKUP
DATABASE is terminating abnormally.

could anyone give me an advice?

Thanks in advance.

score 1 · Accepted Answer · answered Dec 21 '10 at 15:14

1

SQL runs by default under the Localsystem account. that account does not have access to shares on the network as it isn't an authenticated network account.

You should also check the permisions on the share.

answered Dec 21 '10 at 15:14

Steven K.

2,097
16
15

gbn · Answer 2 · 2010-12-18T21:11:13.330

0

Has the SQL Server server account got write access to this share?

edited Dec 18 '10 at 21:11

answered Dec 18 '10 at 21:03

gbn

422,506
82
585
676

Hi, I added 2 slashes \\ but the quoted code gave only 1. I am sure the path is correct. – olidev Dec 18 '10 at 21:05
@JoesyXHN: I've fixed your layout – gbn Dec 18 '10 at 21:09
How can I check whether sql server account got write-access to the share folder? – olidev Dec 18 '10 at 22:12
@JoesyXHN: same as you would any share and NFTS rights. Have as search: it's not straightforwrd to answer here – gbn Dec 22 '10 at 12:11

score 0 · Answer 3 · answered Dec 20 '10 at 00:04

Did you consider backing up to the local drive and having another job (outside of SQL) to copy/move the backup to the network drive?

This way, if there is some problem with the network drive (like that computer is offline, the network is down, etc.) the backup will still happen. I would think that just because the network drive is offline you would not want to skip the backup.

SQL Server 2005 Express: back up to a folder on the network

3 Answers3