REPOST: Deleting backups on a share

charleyevans

I noticed that I can create backups using the UNC path to the share, as described in one of the stickies in this forum. However, I've seen that I receive a 160 Error, unable to delete backups when trying to use ERASEFILES in a script. I checked and the files that should be deleted are in fact not deleted.

Both my account and the account the SQL Backup Service runs under are configured with full permissions on the share. I can go to the share through Windows Explorer using the same path and delete the files manually without issue.

Is this a known issue with SQLBackup? I can't seem to find any information regarding this. Are there any known workarounds? I've figured I'll just use some PowerShell scripting to do the job for me. However, any help in this is greatly appreciated.

Brian Donahue

Hi Charles,

What is your ERASEFILES setting time period configured to be? Is it possible that multiple SQL Backup jobs are contending to delete the same files?

petey

Could you please post the error message? You can find the message in the SQL Backup logs, which by default is stored in C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>.

Thanks.

charleyevans

Brian,

Normally, we just look for anything older than a day to erase. In order to test things, I've been using 1 hour instead. There are no other processes touching this share. So no there is not another backup job that it is competing with. That was my first inclination was I/O contention, followed by account permissions.

Peter,

This is the log...

SQL Backup log file
12/7/2006 12:51:09 PM: Backing up Dev (transaction log) to:
\\server\share\LOG_(local)_Dev_20061207_125109.sqb

12/7/2006 12:51:09 PM: BACKUP LOG [Dev] TO DISK = '\\server\share\LOG_(local)_Dev_20061207_125109.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', ERASEFILES = 1h, COMPRESSION = 1, THREADS = 1

12/7/2006 12:51:10 PM: Backup data size : 832.000 KB
12/7/2006 12:51:10 PM: Compressed data size: 10.000 KB
12/7/2006 12:51:10 PM: Compression rate : 98.80%

Processed 0 pages for database 'Dev', file 'Dev_log' on file 1.
BACKUP LOG successfully processed 0 pages in 0.014 seconds (0.000 MB/sec).

---

12/7/2006 12:51:12 PM: Deleting old backup file: \\server\share\LOG_(local)_Dev_20061206_123003.sqb
12/7/2006 12:51:13 PM: Warning 160: ERASEFILES error: Failed to delete file: \\server\share\\LOG_(local)_Dev_20061206_123003.sqb.

Of course, I've changed the name of the database and the file paths to something generic. Otherwise, this is exactly what we're seeing.

Thanks for any help you can provide.

petey

Can you try running a test command using the command line interface (SQLBackupC.exe), under an account which you know has rights to delete files on the network share, and see if the files are deleted? Thanks.

charleyevans

That worked fine from the command line. The file was erased and the backup was successful. The odd thing here is that the same account has been used throughout this entire process.

petey

Interesting. If the SQL Backup Agent service startup user is identical to the one you used to run the command line interface test, the deletion should have succeeded.

charleyevans

Yes, it should have. Also, interestingly enough, when I run the command using xp_CmdShell I get the same error as when it is run through the red gate sp. I am going to expirement and see if I can get a job to run the batch file.

charleyevans

This ended up being a permissions issue, as I first suspected. The backup service agent account had full permissions on the share, but not on the physical drive. Once full permissions to the drive were granted, things started working. Thanks for all of your help. It led me down the path I needed.