Delete backup issue

pcorujopcorujo Posts: 14
edited January 19, 2011 10:06AM in SQL Backup Previous Versions
Hi,

I am trying to take backups locally as well as copy them to another box and then delete backups older than 2 days on both servers. My current code pasted below does take and copy the backups but never deletes. Is there a problem with my code?

DECLARE @exitcode int
DECLARE @sqlerrorcode int
exec master..sqlbackup N'-SQL "BACKUP DATABASES [*]
TO DISK = ''H:\FullBackups\<AUTO>''
WITH NAME = ''<AUTO>'',
DESCRIPTION = ''<AUTO>'',
VERIFY,
COMPRESSION = 3, COPYTO = ''\\TMPDB103\e$\FullBackupsFromSeoSiteDB'',
ERASEFILES = 2, FILEOPTIONS = 3, THREADCOUNT = 6"',
@exitcode OUTPUT, @sqlerrorcode OUTPUT
IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END


Thank you all in advance.
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    You are using FILEOPTIONS =3, which means you want to delete old files in the COPYTO folder (value 1), and also only files (in both the primary backup folder and the COPYTO folder) which have the archive flag unset (value 2). Is this intentional?

    The archive flag is used by Windows to indicate that the file has not been archived. Usually, users would back up the files to tape, which then unsets the archive flag. Thus, you would use this option (FILEOPTIONS value 2) only when you have an application that archives your backup files, which then clears the archive flag, allowing SQL Backup to delete the file if it's older than the specified interval.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • pcorujopcorujo Posts: 14
    Thank you for you response. We do not have anything backing up to tape so i would imagine that the flag would reflect that we have not archived. So would that mean the archive flag would be unset?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    By default, all files have their 'archive' flag set, meaning that the file is ready for archiving. Once an archiving app has archived the file, this flag is unset. By using FILEOPTIONS value 2, you are telling SQL Backup to only delete files that have been archived.

    In your case, since you do not archive your files, you can leave out FILEOPTIONS value 2, and just use FILEOPTIONS value 1 i.e.
    EXEC master..sqlbackup '-sql "BACKUP ... WITH ..., FILEOPTIONS = 1"'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • pcorujopcorujo Posts: 14
    Thanks again, I truly appreciate your help.
    · Share on Twitter
Sign In or Register to comment.