DB Backup Job

Hi All,

I have a db backup job on SQL Server 2005 server that has a delete step that deletes a backup file that is located on the SQL Server 2000 server. Here is the step:

EXEC master..xp_cmdshell 'del \\DevServerName\Dev-bkups-db\BackupsDB\DBName\DBName_db*.bak /q'

The step doesn't create any errors but it doesn't delete the file. When I run the same command from the command prompt it deletes the file.

I can't figure out what is wrong. Any Idea?

Thanks.Try using Query Analyzer to execute:DECLARE @.foo INT
EXEC @.foo = master..xp_cmdshell 'del \\DevServerName\Dev-bkups-db\BackupsDB\DBName\DBName_db*.bak /q'
SELECT @.foo AS CmdErrorLevelBoth the output and the error level ought to give you clues. My first guess is that the Windows Login being used by xp_cmdshell doesn't have permissions.

-PatP|||I get an Access Denied error and CmdErrorLevel 1. How can I find out which account is used by xp_cmdshell? And what permissions does this accout need?|||it needs sysadmin rights. I think it is using whatever account is running the SQL agent. This account also needs write access to the destination.|||How can I find out which account is used by xp_cmdshell?
sp_xp_cmdshell_proxy_account (http://msdn2.microsoft.com/en-us/library/ms190359.aspx) is where you can set it.what permissions does this accout need?This is the $64,000 question. Microsoft recommends little or no permissions, and I generally agree with them because of the security risks.

I would recommend making the step in the backup job a command step instead of a SQL step. That way you can set the Windows Credentials there, and you don't need to open up xp_cmdshell at all.

-PatP|||How do you set the credentials?|||Check the sql agent service in services.msc. If it's running with a domain account, then that account needs to have access to delete from \\DevServerName\Dev-bkups-db\BackupsDB\DBName\. It doesn't necessarily need admin rights to DevServerName.|||It is running with a local system account.|||local system account can not access network resources.|||Which account should be SQL Agent service startup account?|||typically I have the network guys create a LAN account dedicated to this purpose with a password that does not expire. This last part is very important.|||Thanks for your help.|||How do you set the credentials?The easy way is to change the job owner. There are sneaky ways too, especially if you've installed the Windows Resource Kit or are willing to write a bit of VBA code.

-PatP|||Thrasymachus,

Forgot to ask what are the minimum rights does this account need?

DB Backup Failure

Hello All,

I have created a Maintenance plan using Wizard, In this plan I am doing full backup of 12 DBs to share drive, I created only one step as full backup. And Scheduled this at 7 pm everyday, the
following is the error I am getting in the Logfile, can some one please through some light on this.

" failed with the following error: "Cannot open backup device
\\shq-ss2\sql backup\SQL_PRODDB\WorkFlow_backup_200708291911.bak. Operating
system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not
established correctly.

SQL Server Instance and SQL Agent are running under domain admin account, Share Drive folder has full control by domain admin.

I am the local admin on the SQL Server box, and I have read, write and delete, create permissions on the share drive for backups folder.

What must be the issue?

Thanks

Siebel

Can you try to run the backup of a db using the backup database syntax instead of job and tell us the results........if you have modify privilege in the backup folder it should work fine when you run the backup script as sql server will take your authentication (windows)..........in your case i guess its related to privileges for the shared folder for the startup account as the error is pretty expicit but if your sure try executing the script.........
|||

I am getting same error when I use the DB Backup syntax, I have full control on the shared folder.

What else could be the issue?

Thanks

Siebel

|||

How did you log on to the server, do you use SQL Server or Windows authentication ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

DB Backup Failure

Hello All,

I have created a Maintenance plan using Wizard, In this plan I am doing full backup of 12 DBs to share drive, I created only one step as full backup. And Scheduled this at 7 pm everyday, the
following is the error I am getting in the Logfile, can some one please through some light on this.

" failed with the following error: "Cannot open backup device
\\shq-ss2\sql backup\SQL_PRODDB\WorkFlow_backup_200708291911.bak. Operating
system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not
established correctly.

SQL Server Instance and SQL Agent are running under domain admin account, Share Drive folder has full control by domain admin.

I am the local admin on the SQL Server box, and I have read, write and delete, create permissions on the share drive for backups folder.

What must be the issue?

Thanks

Siebel

Can you try to run the backup of a db using the backup database syntax instead of job and tell us the results........if you have modify privilege in the backup folder it should work fine when you run the backup script as sql server will take your authentication (windows)..........in your case i guess its related to privileges for the shared folder for the startup account as the error is pretty expicit but if your sure try executing the script.........
|||

I am getting same error when I use the DB Backup syntax, I have full control on the shared folder.

What else could be the issue?

Thanks

Siebel

|||

How did you log on to the server, do you use SQL Server or Windows authentication ?

Jens K. Suessmeyer

http://www.sqlserver2005.de