Issues while backing up to the network

  • Vinu verma

    Say Hey Kid

    Points: 685

    I am having some issues dealing with some of the back jobs that run on a SQL Server 2005 machine. We have 3 production servers off which 2 are SQL Server 2000 and the third one being a 2005 box. For both the 2000 boxes both the SQLServer as the SQLServerAgent Services are configured to run on a Logins (not local system) that are specific for running the SQL Jobs. For the third 2005 box there are separte logons for SQLServer as well as the SQLServerAgent. For this particular case the back up job fails when it tries to back up the database over the network.

    I feel this has something to do with the access right of the Logon on which SQL Server Agent is running on the 2005 box. To Test this I enabled the xp_cmdshell on the 2005 box (Server03)

    EXEC master..xp_cmdshell 'Dir \\Servershare\SQLBackup$\Server03\UserDbs'

    EXEC master..xp_cmdshell 'del C:\File.txt'

    EXEC master..xp_cmdshell 'dir c:\'

    EXEC master..xp_cmdshell 'mkdir \\Servershare\SQLBackup$\Server03\Test1'

    EXEC master..xp_cmdshell 'del \\Servershare\SQLBackup$\Server03\Test1*.*'

    For all the above statements I got 'Access Denied' Error

    On the same hand I was able to execute it from the SQL Server 2000 box (Server02)

    EXEC master..xp_cmdshell 'Dir \\Servershare\SQLBackup$\Server03\UserDbs'

    EXEC master..xp_cmdshell 'del C:\File.txt'

    EXEC master..xp_cmdshell 'dir c:\'

    EXEC master..xp_cmdshell 'mkdir \\Servershare\SQLBackup$\Server03\Test1'

    EXEC master..xp_cmdshell 'del \\Servershare\SQLBackup$\Server03\Test1*.*'

    Moreover I am able to access the network share from the Server03 box when i remote terminal into that box using my network id( which is a sysadmin) on the box

    Right now since the back up jobs were failing, i modified the jobs to back it up locally to the box.

    The jobs started to fail as soon the earlier person left the company. Do I need to modify the logon on the SQL 2005(Server03) with the same logon as used in the other 2 boxes (not sure why there was 2 separate logons for this box). Or should add my network id as a proxy account. I would rather not add a user account for running jobs.

    This is what I get from the logs

    Message

    Executed as user: DIG\SQLAGT_NAS. ...33.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  2:00:00 AM  Progress: 2007-09-02 02:00:01.62     Source: {EE39D84A-352E-4E15-BF7D-AB0BA6E31773}      Executing query "DECLARE @GUID UNIQUEIDENTIFIER      EXECUTE msdb..sp".: 100% complete  End Progress  Error: 2007-09-02 02:00:02.07     Code: 0xC002F210     Source: Back Up Database Task Execute SQL Task     Description: Executing the query "EXECUTE master.dbo.xp_create_subdir N'\\Servershare\SQLBackup$\Server03\SystemDBs\master'  " failed with the following error: "xp_create_subdir() returned error 183, 'Cannot create a file when that file already exists.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  Error: 2007-09-02 02:00:02.12     Code: 0xC002F210     Source: Back Up Database Task Execu...  The package execution fa...  The step failed.

  • Kurt W. Zimmerman

    SSCrazy Eights

    Points: 8674

    I had an issue where I wanted to backup several databases across my local network to a file server (read: lots-o-room). In order for me to do this I had to configure SQL Server to be running under a network account ({domain}\sqldaemon) vs. the internal account. By doing this I was able to assign the proper privileges to the share I was writing to so the sqldaemon account so it had write to the share on the file server.

    The error you posted failed because you were trying to create a file that already exists.

    Check how your 2000 box is configured. More than likely sqlserver service is running under a domain user account.

    Good luck and keep us posted.

    Kurt

    DBA

    RHWI

    Poughkeepsie, NY

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • Vinu verma

    Say Hey Kid

    Points: 685

    I have not been able to figure it out. Did some debugging steps from my end

    1) Used runas command and forced a command window which runs under the SQL Agent login

    2) Tried to execute the Job using the Dtexec commd

    C:\>dtexec /SQL "Maintenance Plans\UserDBs" /SERVER SERVER03  /MAXCONCURRENT

    " -1 " /CHECKPOINTING OFF /SET "\Package\UserDBsTrans_Logs.Disable";false /REPOR

    TING E

    Microsoft (R) SQL Server Execute Package Utility

    Version 9.00.3042.00 for 32-bit

    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

    Started:  2:17:29 AM

    Error: 2007-09-06 02:17:31.15

       Code: 0xC002F210

       Source: Back Up Database Task Execute SQL Task

       Description: Executing the query "EXECUTE master.dbo.xp_create_subdir N'\\Servershare\SQLBackup$\Server03\UserDBs\Trans_Logs\GeoData'

    " failed with the following error: "xp_create_subdir() returned error 183, 'Cann

    ot create a file when that file already exists.'". Possible failure reasons: Pro

    blems with the query, "ResultSet" property not set correctly, parameters not set

     correctly, or connection not established correctly.

    End Error

    Error: 2007-09-06 02:17:31.24

       Code: 0xC002F210

       Source: Back Up Database Task Execute SQL Task

       Description: Executing the query "BACKUP LOG [GeoData] TO  DISK = N'\\Servershare\SQLBackup$\Server03\UserDBs\Trans_Logs\GeoData\GeoData_backup_2007090602

    17.trn' WITH NOFORMAT, NOINIT,  NAME = N'GeoData_backup_20070906021731', SKIP, R

    EWIND, NOUNLOAD,  STATS = 10

    " failed with the following error: "Cannot open backup device '\\Servershare\SQL

    Backup$\Server03\UserDBs\Trans_Logs\GeoData\GeoData_backup_200709060217.trn'.

     Operating system error 5(Access is denied.).

    BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with

    the query, "ResultSet" property not set correctly, parameters not set correctly,

     or connection not established correctly.

     End Error

    This previous DBA was the owner of this job. Do you want me to delete the job and start from fresh. My id has logon writes to the network share. Will adding a proxy account to use my network solve the issue. Please help

    Vinu Verma

  • Kurt W. Zimmerman

    SSCrazy Eights

    Points: 8674

    Vinu; If you read the error message you are trying to create a file that already exists, which is preventing the backup to run.

    I would suggest recreating the backup from ground up and giving it a try. There is no magic creating backup Maintenance Plans. Step through the wizard.

    If you have any problems post them here and I (or someone else) will help you out.

    Kurt

    DBA

    RHWI

    Poughkeepsie, NY

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • jnsnfl

    SSC Enthusiast

    Points: 169

    I have had to use command line backup routines and had found similarly problems with the generic "device not ready" error message.

    I found after a bit of embarrassment that while I was logged onto the server as an authorized administrator I actually had trouble with the permissions on the server for my profile. I was able to confirm via window actions the navigation and creation of folders (or removal as the case might be) but from the command line I couldn't.

    The result was I had to go to windows and set the permissions for my user (or the designated service account I created) to have the true service level authority to add, drop or otherwise alter directory / folder names.

    Once this was cleared up I no longer recieved the "device not ready" error.

    I'm just sayin' 😛

    Hakim's Razor issue? the most obvious answer may actually be it....

  • Bhuvnesh

    SSC Guru

    Points: 59344

    Vinu verma (9/6/2007)


    This previous DBA was the owner of this job.

    i would suggest (recommended every where ) that we should use service account so that it wont be depended on any individual.So when you do the fresh building use a separate service account.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • chris 24158

    SSC Enthusiast

    Points: 144

    I just had this exact problem, where the SQL Agent account was a domain account. Problem turned out to be somehow permissions got yanked for that account. The description for error 183 isn't quite correct.

  • wilks

    SSC Veteran

    Points: 295

    chris 24158 - Friday, January 13, 2012 9:38 AM

    I just had this exact problem, where the SQL Agent account was a domain account. Problem turned out to be somehow permissions got yanked for that account. The description for error 183 isn't quite correct.

    I had the same error - this occurred because there were db backup files without file extensions, but with the same name as the folders the job was trying to create. So it failed to create the folders.

Viewing 8 posts - 1 through 8 (of 8 total)

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