Multiple COPYTO with different delete timeframes

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Multiple COPYTO with different delete timeframes

Postby AHertweck » Fri Apr 11, 2014 7:41 pm

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
 
Posts: 2
Joined: Fri Apr 11, 2014 7:36 pm

Postby petey » Mon Apr 14, 2014 4:24 pm

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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Thanks!

Postby AHertweck » Mon Apr 14, 2014 4:38 pm

It didn't occur to me to break those out into separate commands.

Thanks!
AHertweck
 
Posts: 2
Joined: Fri Apr 11, 2014 7:36 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 0 guests