Problem backing up to network drive

Question

Post reply

Problem backing up to network drive

johnmcp

SSCommitted

Points: 1969
More actions
August 26, 2008 at 3:38 am

#124990

I'm having trouble backing up to a network drive on a particular server.
My backup command:
backup database fa_sfds to disk=' ' WITH FORMAT
Getting this error:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device , . Device error or device off-line. See the SQL Server error log for more details.
I have both the SQL Server service account and SQL Server agent account running under a domain account that has access to the directory I'm trying to create the backup in.
I've also tried mapping a drive and changing the file path in my backup command but this doesn't work either.
Really stumped on this one because it works fine on all my other servers. Would love to hear any suggestions.
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

Does the SQL Service Server Account have write access to the target folder at both the Share and File-System level?

Do you get the errors detailed in the MS KB article: http://support.microsoft.com/kb/207187

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

John H Marsh SSChampion Points: 11671 More actions · Answer 2

Hello again,

If your share and File-System Permissions do seem fine, and you are really stuck, then the following article suggests an "if all else fails" option. As it removes security and requires a registry update, it should only be used as a last resort: http://windowsitpro.com/article/articleid/14025/why-cant-i-backuprestore-my-sql-server-database-to-a-share-on-another-server.html

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

johnmcp SSCommitted Points: 1969 More actions · Answer 3

Hi,

I've verified that the account SQL Server is running under has write access at the share level and the file system level so there's no problem there.

The KB article does indeed describe the issue I'm having but the steps in the workaround are already in place on the server i.e. SQL Server is running under a domain account and this domain account has the necessary permissions on the destination directory.

John H Marsh SSChampion Points: 11671 More actions · Answer 4

Hello,

I guess you could temporarily try the registry update and then back it out. At least that way you would be sure to eliminate permissions as the cause (or not).

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

johnmcp SSCommitted Points: 1969 More actions · Answer 5

I just tried the registry update and it still doesn't work.

So I guess that implies that the problem is not permissions related right?

John H Marsh SSChampion Points: 11671 More actions · Answer 6

Hello,

Yes, that implies it is not a Folder permissions issue. Just to double check though, that you did restart the "Server" Service on the Target Server, after making the Registry change? If so, then a couple of other things to check:-

1) If you cut and past the Target Folder Path from your Backup statement into Windows Explorer (on the SQL Server) does it work?

2) Can you sign on to the Target Server using the SQL Server Service Account (assuming you know the password), just to check if it is an authentication issue.

3) Check the Event Logs on the Target Server. May be you get a Failure Audit logged at the time that you attempt the backup?

BTW - Are there any spaces in the Target Folder Path?

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

johnmcp SSCommitted Points: 1969 More actions · Answer 7

Hi,

Yes I restarted the server service. However, one thing I was slightly unsure about was how to change the NullSessionShares value. It is a REG_MULTI_SZ type so I firstly typed in the share name in hex, then secondly I copied the value from the Shares key (which is just underneath the parameters key). Neither method worked but perhaps I was wrong in both cases? The article is a little unclear on this.

To answer your other questions:

1) Yes I can access the folder through Windows Explorer.

2) Yes I can sign in to the target server with the SQL Server Service Account.

3) There is no entry in the Event Logs relating to Failure Audit.

There are spaces in the target path folder. But this is enclosed in the SQL command so it shouldn't make any difference.

John H Marsh SSChampion Points: 11671 More actions · Answer 8

Hello,

You should be able to type the Share name into the Registry key in plain text

Although spaces in the path shouldn’t be a problem, I have seen posts claiming that they do cause issues. Can you try backing up to a path without spaces in the name?

Also what OS does the Target Server have? Is the Target Folder on an NTFS partition?

Finally could you please post the associated error from your SQL Server Log?

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

johnmcp SSCommitted Points: 1969 More actions · Answer 9

Hi,

I typed the share name in plain text but it didn't make any difference.

I made a temporary directory without spaces, same result.

The target partition is NTFS on a Windows Server 2000 machine.

Here is the error from the log:

BackupDiskFile::CreateMedia: Backup device '\\msllava14rds\RDS Backups\Lava6' failed to create. Operating system error = 5(Access is denied.).

which is very confusing because it still suggests a permissions issue. I despair :crying:

John H Marsh SSChampion Points: 11671 More actions · Answer 10

Good Morning,

For me the usual suspects are (in order of likelihood):-

1) Share or File-System Permissions

2) Authentication failure

3) Network issues

4) Something Weird

The tests so far seem to be ruling out the first three, but as you stated the SQL Error message suggests it is permission related.

One more test that you could try - Log on to the SQL Server's Windows Server as the SQL Service Account. Use Windows Explorer to browse to the Target Folder. Try to manually create a test text file in the Target Folder.

In the meantime, I remembered seeing a post once from somebody else having problems backing up to a Windows 200 Server. I will see if I can find that one again.

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

darren.woolston Old Hand Points: 333 More actions · Answer 11

Hi mcphillipsjohn,

I share your pain, while I didn't have exactly the same problem as yourself, but have a look at my topic from a few months ago....

The solution was to increase the timeout session, the destination server was too busy to respond quick enough for SQL liking.

It may not be that, but worth a shot. Good Luck.

rajankjohn SSCertifiable Points: 5061 More actions · Answer 12

does your policies permit to login interactively to the server console with the service account? If so, do that and confirm the network drive permissions. Else set your login account as the service account and try to isolate that the network drive permission is not the culprit. Also, try creating a backup device to the path initially and then backup to the created device.

johnmcp SSCommitted Points: 1969 More actions · Answer 13

Hi Darren,

Thanks for the suggestion, I'm going to try it now. I'll report back shortly...

johnmcp SSCommitted Points: 1969 More actions · Answer 14

Rajan John/John Marsh,

I've logged to Windows with SQL Server Service Account and created test files on the target server without any problem so I'm 100% sure that the account has adequate permissions.

I tried also to create a backup device a few days ago and backup to this device but this brought the same error.

I will try Darren's suggestion and see how that goes.