REPOST: Deleting backups on a share

charleyevanscharleyevans Posts: 15
edited December 11, 2006 11:33AM in SQL Backup Previous Versions
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.
Thanks,

Charles Evans

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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?
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.
    Thanks,

    Charles Evans
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.
    Thanks,

    Charles Evans
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.
    Thanks,

    Charles Evans
  • 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.
    Thanks,

    Charles Evans
Sign In or Register to comment.