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

Multiple COPYTO with different delete timeframes

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?
AHertweck
0

Comments

2 comments

Sort by
  • petey
    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.
    petey
    0
  • AHertweck
    It didn't occur to me to break those out into separate commands.

    Thanks!
    AHertweck
    0

Add comment

Please sign in to leave a comment.