Cann't take backup on Network Drive

Question

Cann't take backup on Network Drive

Shahzadi

Default port

Points: 1479
More actions
March 17, 2008 at 9:50 am

#183350

HI
i am trying to take a backup on Network drive but i am getting following error message. althought when i try to type the address in window explorer. i can access the specific folder. so that there shouldn't be network security issue.... and even thought the backup device name is correct.
when i try to take backup on local disk, it work's fine....
Executed as user: NT AUTHORITY\SYSTEM. Cannot open backup device 'FullBackup(\\0.0.0.0\SQL_Backups\FullBackups\FullBackup.bak)'. Operating system error 5(error not found). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

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

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

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 1

Have you confirmed that the SQL account backing up to the network drive has sufficient privileges to write to that drive?

DAB

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

My understanding is that the NT AUTHORITY account can be used only on your local machine. Try to change the account used in your backup job to your account and see if it works.

Ramji29 Hall of Fame Points: 3832 More actions · Answer 3

Hello Guys,

I'm also facing the same error and I've cross check that All my sql server services is running under the administrator login.

FYI: My boht the server i.e. The SQL Server and the server where I'm trying to take the backup are stand alone servers and the folder which I'm using for backup on backup server is accessible from sql server as I've added the D drive of backup server as map drive on sql server. but still when I'm executing the belwo command I'm getting the following error.

EXEC sp_addumpdevice 'disk', 'Rohit_Backup_Set', '\\10.2.84.203\Rohit\Rohit_Full_Backup.bak'

Backup database Rohit To Rohit_Backup_Set

Error:

Msg 3201, Level 16, State 1, Line 1

Cannot open backup device 'Metrics_Commercial_20_Backup_Set(\\10.2.84.203\Metrics_Commercial_20\Metrics_Commercial_20_Full_Backup.bak)'. Operating system error 1326(Logon failure: unknown user name or bad password.).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Can anybody help me in this regard?

Rohit

tosscrosby SSCoach Points: 18045 More actions · Answer 4

It's definitely permissions, either the account doesn't have access to the server or to the particualr share on that server. Check these links.

http://www.microsoft.com/technet/archive/winntas/support/acdenid2.mspx?mfr=true

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2288846&SiteID=17

-- You can't be late until you show up.

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 5

those are permissions or access errors, but keep in mind that it's likely the backup will fail if there is any network issue. FTP and other transfer methods tolerate some delays in the backup process. SQL Server has NO tolerance for delays.

Backup locally, script a copy to the network.

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 6

Hi Shahzadi

I would troubleshoot in the following manner:

Click Start - Run - in the open dialogue paste the following:

\\0.0.0.0\SQL_Backups\FullBackups

Can you get to this path? -

If NO then setup file sharing / access

If YES then try create a textfile in this location

If you can create a text file and save it then security as the user logged on is ok

If the job is run from Management Studio or a job - ensure that the user logged on and the account that executes the jobs has sufficient access right (maybe even create a new user - grant rights to server (\\0.0.0.0\SQL_Backups\FullBackups\) and also on the database with permissions to run backups.

Let us know how if works out

Thanks

Kevin

Ramji29 Hall of Fame Points: 3832 More actions · Answer 7

Hello Kevin,

I'm able to access the network shared folder through windows and able to read and write the data, but when I'm trying the sme thru command prompt as u suggested then its throwing the below error.

"CMD does not support UNC paths as current directories"

What should I do in this case?

Rohit

rajankjohn SSCertifiable Points: 5061 More actions · Answer 8

when you access the network dive from start--> run --"location", you are using your windows credentials with which you logged in, to access the location. When you run backup to the same network drive, the permissions of the SQL Server service account is used to backup to the remote location.

In your case it is clear that you havent changed your default service account for SQL Server service i.e. LocalSystem. Change the service account to something that will have read/write access to the remote location, and you will get the issue solved.

http://rajanjohn.blogspot.com

Ramji29 Hall of Fame Points: 3832 More actions · Answer 9

Rajan,

Both the servers i.e. SQL Server and the server where I wanna to take backup are stand alone servers and are not in any domain.

Now at both the server I'm using the built in Administrator a/c to logon and I've already changed all the sql server services to start using 'Administrator' a/c, not the local system a/c and through start--> run --"location" I'm able to access the remote server and able to cut, copy and paste the files there.

I think I'm able to explain the problem I'm facing.

Rohit

rajankjohn SSCertifiable Points: 5061 More actions · Answer 10

if both the servers are standalone ( I mean not part of any domain), create a local login on both the servers, say "SvcSQL" with the same password. Add these accounts to the Administrators group of the respective servers. Change the service account of SQL Server service to SvcSQL.

To test the access, you can once login using SvcSQL and test the accessibility from Start --> Run --> "location". If possible, create a backup device exclsuively using sp_addumpdevice to the location, and do the backup to the device.

http://rajanjohn.blogspot.com/

Shahzadi Default port Points: 1479 More actions · Answer 11

Hi Kevin.

thanks, it's works..

Thanks alot.............................

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 12

Hi

I am getting to understand a bit better.

It seems as if because the machine is a stand alone - the scheduler does not have sufficient rights at the execution of the job.

I had a similar problem a while back where we needed to output a text file for a 3rd party company onsite - but the trick was that this destination pc was not on our domain - but on our network.

Everything we tried failed due to security rights etc...

Solution

======

Our solution was as follows:

We created an SSIS package that output the text file to a drive letter Q:So in the job - step 1 we mapped to this stand alone pc using the following:

Step type: Operating System (cmdExec)

Run as: SQL Agent Service Account

Command:

net use q: \\172.16.10.96\sfs p@ssword /user:jhb_sfs_xp\sfsjhb

The 2nd step then makes use of the drive letter Q:(the job fails on step 1 each time - but step 2 succeeds)

The above worked for us as the job now at run time has security credentials and the output path is accessible on this new server.

Try making use of step 1 and have your backup as a script in step 2 that writes to the mapped Q:\ drive

(create a user on the destination pc if required with rights etc)

Give it a try and let us know if it works

Thanks

Kevin

Ramji29 Hall of Fame Points: 3832 More actions · Answer 13

Hello Kevi,

Can you explain your solution with some more description, as

The syntax of the command

net use q: \\172.16.10.96\sfs p@ssword /user:jhb_sfs_xp\sfsjhb

and what exactly you mean by

We created an SSIS package that output the text file to a drive letter Q:

u created a Job or a SSIS package?

Nothing is working for me as of now as I tried the below steps.

I've created two users both at SQL Server and backup server with same user name and password, all the SQL services in SQL server are running using same login, If I'm tring through Run--> and \\0.0.0.0\Backups then I'm able to create a new folder, text file etc. but when I'm tring it through management Studio and tring to take the backup it failing with below error in error log.

--------

Date,Source,Severity,Message

04/28/2008 05:38:59,Backup,Unknown,BACKUP failed to complete the command BACKUP DATABASE master. Check the backup application log for detailed messages.

04/28/2008 05:38:59,Backup,Unknown,Error: 3041 State: 1.

04/28/2008 05:38:59,spid53,Unknown,BackupDiskFile::CreateMedia: Backup device '\\109.120.100.81\Backups\master-Full Database Backup.bak' failed to create. Operating system error 5(Access is denied.).

04/28/2008 05:38:59,spid53,Unknown,Error: 18204 State: 1.

04/28/2008 00:00:33,spid13s,Unknown,This instance of SQL Server has been using a process ID of 1348 since 4/23/2008 3:09:57 AM (local) 4/23/2008 10:09:57 AM (UTC). This is an informational message only; no user action is required.

--------

Any help will be appreciated.

Rohit

Ramji29 Hall of Fame Points: 3832 More actions · Answer 14

When I tried this method, I got below error in my job history.

Executed as user: OAKSVR004\Prod1. The syntax of this command is: NET USE [devicename | *] [\\computername\sharename[\volume] [password | *]] [/USER:[domainname\]username] [/USER:[dotted domain name\]username] [/USER:[username@dotted domain name] [/SMARTCARD] [/SAVECRED] [[/DELETE] | [/PERSISTENT:{YES | NO}]] NET USE {devicename | *} [password | *] /HOME NET USE [/PERSISTENT:{YES | NO}]. Process Exit Code 1. The step failed.

Can any body explain this syntax with proper names.

Rohit