September 5, 2007 at 12:20 am
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.
September 5, 2007 at 9:21 am
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
September 6, 2007 at 7:55 am
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
September 10, 2007 at 1:01 pm
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
November 23, 2010 at 2:09 pm
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....
November 25, 2010 at 11:47 pm
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;-)
January 13, 2012 at 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.
June 28, 2018 at 1:09 am
chris 24158 - Friday, January 13, 2012 9:38 AMI 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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply