none
SQL Server 2008 Maintenance plan will not delete bak and trn files

    Question

  • I have a sql 2008 maintenance plan to backup databases and to delete old bak files.  The db backup works but the delete does not.  I do not get any error messages, all steps marked successful.  So, I copied the sql statement EXECUTE master.dbo.xp_delete_file 0,N'Z:\SQL BACKUPS\',N'bak',N'2010-11-14T12:43:35',1 and tried to execute in a query window.  I have a successful completion message, BUT the files are not deleted.  I have ensured that the extension is bak and the folder is correct and the account has sysadmin on the server and is a sql sa.  I have tried many options and no success.  

    We are version   Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM).

    Any help would be appreciated.  Thanks!

    Monday, November 15, 2010 5:49 PM

Answers

  • Thanks to everyone for the replies.

    What solved my problem:

    I removed the AD account from the Windows administrator group, then added the AD account as SQL login and gave the account  sa privileges.  Now my maintenance plan jobs run as expected.


    SRoscoe DBA
    Monday, November 22, 2010 4:11 PM

All replies

  • Please check if SQL Server Service Account has full control on your "Z:\SQL Backups\" folder.
    Thanks. Mohit. -------------------------------------- MCTS: SQL Server 2005, MCITP: Database Administrator. http://sqllearnings.blogspot.com/
    Monday, November 15, 2010 6:10 PM
  • To confirm if the permissions are properly set on the folder, try executing this command from SSMS.

     

    xp_cmdshell 'md Z:\SQL Backups\TestDir'
    




    Pradeep Adiga
    My blog: http://www.sqldbadiaries.com

    Recent posts on my blog
    Monday, November 15, 2010 7:09 PM
  • yes, the service account has full control.  I have tried running this with my own credentials (windows sysadmin and sql server sa) and can still not execute the xp_delete_file
    SRoscoe DBA
    Monday, November 15, 2010 7:13 PM
  • Just to confirm, are the backup directly located on Z:\SQL Backups\TestDir folder or configured to go to a sub-folder?


    Pradeep Adiga
    My blog: http://www.sqldbadiaries.com

    Recent posts on my blog
    Monday, November 15, 2010 7:21 PM
  • Here are the results from the xp_cmdshell:

    The directory or file cannot be created.
    Error occurred while processing: Backups\TestDir.
    NULL

    I ran the above with my credentials (sa and sysadmin(windows))

    The backups go directly to Z:\SQL Backups, no subfolders


    SRoscoe DBA
    Monday, November 15, 2010 7:25 PM
  • The thing is you executing the command as SYSADMIN and WINDOWS ADMIN does not grantee you can write to that folder.  That is why I asked you to check out SQL Server Service account.  If that service account does have access to the SQL Server; run Sysinternals FILEMON.  And run your xp_delete_file command again and watch which account is actually executing the command.  There might be lost credentials issue and you have to reapply your security rights for SQL Server.
    Thanks. Mohit. -------------------------------------- MCTS: SQL Server 2005, MCITP: Database Administrator. http://sqllearnings.blogspot.com/
    Monday, November 15, 2010 7:33 PM
  • Is the backup files older than  the specified time in the cleanup task?

    If the files are not older than the specified time, it will not delete.


    Regards,

    Sandesh Segu

    http://www.SansSQL.com

    SansSQL

    ↑ Grab this Headline Animator

    Tuesday, November 16, 2010 3:20 PM
  • yes the files are older.
    SRoscoe DBA
    Tuesday, November 16, 2010 3:23 PM
  • If u have used sub-directories to the backup, then u will have to check the checkbox "Include first level sub-directories" in the maintanance clenup task and also the file extn should be only bak and not ".bak".

    also can you post the backup time present in the backup file name and also the xp_delete_file command you are using to delete the files?

     


    Regards,

    Sandesh Segu

    http://www.SansSQL.com

    SansSQL

    ↑ Grab this Headline Animator

    Tuesday, November 16, 2010 3:36 PM
  • the xp delete command is in the initial post.  I do not have sub-directories and the file extension is bak.

    The time stamp is msdb_backup_2010_11_13_0000007_0290207.bak.  I am thinking this is probably a permissions error. 

    MORE INFO:  Server is Windows 2008, SQL Server is 2008.  I have the same job set up on a Windows 2003 server with SQL Server 2008 and it works fine.


    SRoscoe DBA
    Tuesday, November 16, 2010 3:46 PM
  • May be a permission issue. in my setup its working file.

    Just check if you can try this command. This will delete all the .bak files in the msdb folder.

    EXECUTE master.dbo.xp_delete_file 0,N'C:\Backup\msdb',N'bak'
    


    Regards,

    Sandesh Segu

    http://www.SansSQL.com

    SansSQL

    ↑ Grab this Headline Animator

    Tuesday, November 16, 2010 4:34 PM
  • I do not have an msdb folder.  I only have the SQL Backup folder (no subfolders).

    I moved some backup files to a different location(path) and tried the following command:

    EXECUTE master.dbo.xp_delete_file 0,N'Y:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backups',N'bak'

    I get the message that command completed successfully, but no files were deleted. 


    SRoscoe DBA
    Tuesday, November 16, 2010 7:33 PM
  • Does changing bak to BAK in the above query work?


    Pradeep Adiga
    My blog: http://www.sqldbadiaries.com

    Recent posts on my blog
    Tuesday, November 16, 2010 7:43 PM
  • same results
    SRoscoe DBA
    Tuesday, November 16, 2010 8:19 PM
  • Hi sroscoe,

     

    Since Maintenance Plan is a package of SQL Server Integration Services, when we run it from SQL Server Agent, it actually runs under the context of account specified for this step.

     

    Therefore I would like to recommend that you check if the account used by certain step of this Maintenance Plan has sufficient privilege to perform this opration.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Wednesday, November 17, 2010 8:54 AM
  • ok....I have found out the following:

    1.  When you execute the xp_delete_file, the account that is actually running it is the SQL Server service account.  In our case, that is an AD account.

    2.  The SQL Server service account must be a windows sysadmin (what happened to using the lest privileges, our security office will not let this fly)

    I think I will be contacting microsoft to see if there is any way around this, else I will have to set up a script and run it in a scheduled task to perform this delete.


    SRoscoe DBA
    Thursday, November 18, 2010 8:59 PM
  • Hi sroscoe,

    Any progress?


    Regards,
    Tom Li
    Friday, November 19, 2010 1:39 AM
  • Do you have any experience with Active Directory GPO's, or someone on staff who understands them fairly well. It may be work to design a GPO specifically for your SQL servers or put them in their own organizational unit. That way the Domain Sql Service account can be specifically assigned special rights that become applied as the final setting of the RSOP (Resultant set of policy) which has a GUI available as rsop.msc.

    Of note is the fact that the SQL instances host machines local security policy setting will be applied if the specific user rights assignment setting detailed in the linked article remains unconfigured at the domain level. However this final policy application will not show in the RSOP tool becasue the RSOP tool is based upon a domain level Active Directory query.

    SQL account rights are described here.

    http://msdn.microsoft.com/en-us/library/ms143504.aspx#Review_NT_rights

    I have done as described and the sql and sql agent services can run as a regular domain user that is also placed in the local machines backup operators group. These accounts can then be placed through out the network with appropriate permissions where needed for log shipping, backup transfer to network folders etc.

     

     

    Friday, November 19, 2010 2:49 PM
  • Thanks to everyone for the replies.

    What solved my problem:

    I removed the AD account from the Windows administrator group, then added the AD account as SQL login and gave the account  sa privileges.  Now my maintenance plan jobs run as expected.


    SRoscoe DBA
    Monday, November 22, 2010 4:11 PM
  • We're having the same issue, but the domain account for agent service, and the account I log in with both have local admin rights where the files are stored, public/sa rights, etc.  Any other ideas?
    Tuesday, January 03, 2012 8:58 PM
  • This is a permission issue.

    I was having the same issue and i'd tested with 2005/2008 maintenance plan and the key is the account settings for the sql server agent, this account must be sysadmin in sql and have permissions in the current folder, i actually setup .bak and .trn files and when made this changes the job actually deleted the requested files.

    If you have any issue can contact me.

    Have a nice day.

    Thursday, January 12, 2012 3:24 PM
  • Hello --

    I am in the process of setting up a similar plan on our server. Do you have a way to provide a copy of the plan so I can implement it on our system?

    Thanks.

    Tuesday, July 31, 2012 3:51 PM
  • Even though this post is real old, but I am in the same situation. My maintenance job runs successfully, but does not delete the files. When you say"

    "I removed the AD account from the Windows administrator group, then added the AD account as SQL login and gave the account  sa privileges.  Now my maintenance plan jobs run as expected."

    What AD you are talking aboout? Can you please explain in details the steps you took to make it to work? Thanks.

    Wednesday, November 14, 2012 4:57 PM
  • I inherited a server with this issue, to resolve the issue we ended up changing .bak (note the dot) to bak (removed the dot).

    Monday, November 19, 2012 5:25 PM
  • me too, all rest is correct. changing from ".bak" to "bak" solved the issue.

    the strange thing is that nothing gives any error.

    Monday, July 15, 2013 7:26 PM
  • Another possible reason for this failure is the Owner of the subplan may not have the correct permissions. Switch the Owner to the SQL server service account. 

    See if you have a similar error log (would be your subplan name and your domain\username):

    SQL Server Scheduled Job 'Flat File Backups Cleanup Monthly.Subplan_1' (0x81003D3945721C49BD0CD38C14D48EDD) - Status: Failed - Invoked on: 2013-09-11 10:53:07 - Message: The job failed.  The owner (DOMAIN\username) of job Flat File Backups Cleanup Monthly.Subplan_1 does not have server access.

    Wednesday, September 11, 2013 3:03 PM