Results 1 to 14 of 14

Thread: DB Backup Job

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: 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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try using Query Analyzer to execute:
    Code:
    DECLARE @foo INT
    EXEC @foo = master..xp_cmdshell 'del \\DevServerName\Dev-bkups-db\BackupsDB\DBName\DBName_db*.bak /q'
    SELECT @foo AS CmdErrorLevel
    Both 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

  3. #3
    Join Date
    Jul 2004
    Posts
    268
    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?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by inka
    How can I find out which account is used by xp_cmdshell?
    sp_xp_cmdshell_proxy_account is where you can set it.
    Quote Originally Posted by inka
    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

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How do you set the credentials?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2007
    Posts
    9
    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.

  8. #8
    Join Date
    Jul 2004
    Posts
    268
    It is running with a local system account.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    local system account can not access network resources.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jul 2004
    Posts
    268
    Which account should be SQL Agent service startup account?

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Jul 2004
    Posts
    268
    Thanks for your help.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    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

  14. #14
    Join Date
    Jul 2004
    Posts
    268
    Thrasymachus,

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •