Multiple COPYTO with different delete timeframes

AHertweckAHertweck Posts: 3 New member
edited April 14, 2014 11:38AM in SQL Backup Previous Versions
I'm looking to do something like this:

Daily backups held for 14 days
BiMonthly backups held for 6 months
Yearly backups held for 4 years

DECLARE @exitcode int
DECLARE @sqlerrorcode int
Declare @backupCMD varchar(4000) = ''
,@biMonthly varchar(500) = ''
,@yearly varchar(500) = ''

Select @biMonthly = case when datepart(day, getdate()) in (1,15) then ', COPYTO = ''Link NAS'', ERASEFILES = 190, FILEOPTIONS = 5' else '' end
,@yearly = case when DATEPART(dayofyear, getdate()) = 1 then ', COPYTO = ''LINK NAS'', ERASEFILES = 1825, FILEOPTIONS = 5' else '' end

set @backupCMD = '-SQL "BACKUP DATABASE [MyDB] TO DISK = ''L:\Backup\<AUTO>.sqb'' WITH ERASEFILES_ATSTART = 7' +
', FILEOPTIONS = 4, CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 4' +
', COPYTO = ''\\SERVER\SQLBACKUP\'', ERASEFILES = 14, FILEOPTIONS = 5' +
@biMonthly +
@yearly +
', INIT, THREADCOUNT = 23"'

Select @backupCMD

EXECUTE master..sqlbackup @backupCMD, @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

Is this supported? Should I approach this by a differnt means?

Comments

  • peteypetey Posts: 2,358 New member
    SQL Backup can apply different retention settings to either:

    - backup files found on local and network shares (ERASEFILES and ERASEFILES_REMOTE) OR
    - the backup and copyto locations (ERASEFILES_PRIMARY and ERASEFILES_SECONDARY)

    What you could do is set up the backup task to use the ERASEFILES option to delete old files in the primary backup folder. You can't use ERASEFILES_REMOTE nor ERASEFILES_SECONDARY as it will apply to both the 6-month and 4-year backup folders. Instead, use the standalone file deletion procedure to clean up files in those 2 folders. You would need to set up a daily SQL Agent job that runs something like this:

    EXEC master..sqlbackup '-sql "ERASE FULL_BACKUPS FOR [mydb] FROM DISK = [folder that holds 6 months worth of backups] KEEP = 190"'
    EXEC master..sqlbackup '-sql "ERASE FULL_BACKUPS FOR [mydb] FROM DISK = [folder that holds 4 years worth of backups] KEEP = 1825"'

    Another suggestion is if you back up a lot of databases, I would suggest you place the backup files in their own database-specific folder using the DATABASE tag i.e. instead of '... TO DISK = ''L:\Backup\<AUTO>.sqb'' ', use '... TO DISK = ''L:\Backup\<DATABASE>\<AUTO>.sqb''. This avoids SQL Backup having to scan all the files in a folder that holds backups from many different databases during the deletion process, thus saving disk and processing cycles and speeding up things considerably.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • AHertweckAHertweck Posts: 3 New member
    It didn't occur to me to break those out into separate commands.

    Thanks!
Sign In or Register to comment.