How can we help you today? How can we help you today?

Delete backup issue

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.
pcorujo
0

Comments

4 comments

Sort by
  • petey
    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.
    petey
    0
  • pcorujo
    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?
    pcorujo
    0
  • petey
    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"'
    petey
    0
  • pcorujo
    Thanks again, I truly appreciate your help.
    pcorujo
    0

Add comment

Please sign in to leave a comment.