June 10, 2014

SQL Maintenance Cleanup Task Not Deleting Old Database BAK Files

If you break permission inheritance on your SQL backup folder, and if you have a maintenance plan that uses subfolders to backup your databases by name, you could render your Maintenance Cleanup Task inoperable. If you encounter this problem, you probably noticed that the cleanup task appears to run successfully, but in reality it doesn’t do anything. Old database backup files still exist. If you change the cleanup task to point to one of the database backup subfolders, suddenly it starts working, well, at least for that one database subfolder. If you try to browse to that subfolder using the browse button in the cleanup task, you will get the following error:

Locate Folder – [YOUR SQL SERVER NAME]

X:\Program Files\Microsoft SQL Server\xxxxx\xxxxx\backup\subfolder
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

If all of this sounds eerily familiar to you, try doing this to fix your problem:

THE FIX

You need to give the MSSQLSERVER account full access to the root backup folder and all subfolders. In our case, MSSQLSERVER already had full access to the root backup folder, but when inheritence was broken, this permission did not flow to the subfolders. I’m still not sure why that happened.

  1. Right-click your root backup folder (wherever they may exist) and click Properties.
  2. Click the Security tab, then click the Edit button, then click the Add button.
  3. Make sure the From this location: field is pointing to your SQL server and not your Active Directory domain.
  4. Type “NT SERVICE\MSSQLSERVER” in the Enter the object names field without the quotes and click OK.
  5. You might be presented with a Multiple Names Found prompt. Choose MSSQLSERVER and click OK.
  6. Put a check in the Allow box next to Full control and click OK and that’s it. The rights will filter down to all subfolders and files (assuming you didn’t previously break inheritance further down the chain). Otherwise you will need to add MSSQLSERVER via the Advanced Security menu (Advanced button) and check the box next to Replace all child object permissions with inheritable permissions from this object.

Execute your Maintenance Cleanup Task and old backup files should now successfully delete.

Get The Roo In Your Inbox

Receive The Roo Report only when new content is posted. We promise the Roo will be on his best behavior!
Sell Art Online

Please share your thoughts