Cann't take backup on Network Drive

  • 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.

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

    DAB

  • 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.

  • 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

  • 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.

  • 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.

  • 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

  • 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

  • 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

  • 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

  • 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/

  • Hi Kevin.

    thanks, it's works..

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

  • 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

  • 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

  • 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

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

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