Red Gate forums :: View topic - Multiple COPYTO with different delete timeframes
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

Multiple COPYTO with different delete timeframes

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
AHertweck



Joined: 11 Apr 2014
Posts: 2

PostPosted: Fri Apr 11, 2014 7:41 pm    Post subject: Multiple COPYTO with different delete timeframes Reply with quote

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?
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Mon Apr 14, 2014 4:24 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
AHertweck



Joined: 11 Apr 2014
Posts: 2

PostPosted: Mon Apr 14, 2014 4:38 pm    Post subject: Thanks! Reply with quote

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

Thanks!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group