0

We have a full backup hourly, which really builds up, even when we have set our Ola Hallengren statements to 'cleanuptime = 168' which removes old backups > 1 week. This is still too many backups to keep and our disk space is limited. We don't want to change the backup frequency or type. We need a clean up that all backups older than 24 hours, except for one for each day that is performed at midnight. We have considered using File Tables looking at the backup folder, but would prefer to use native cleanup processes. Is what we want possible through Maintenance Plans or even Ola's scripts?

cloudsafe
  • 2,444
  • 1
  • 8
  • 24
  • Have you tried 'cleanuptime=24' – Matt Evans Sep 23 '19 at 11:49
  • Wouldn't transaction logs be better here , rather than a full backup every hour? – Thom A Sep 23 '19 at 12:10
  • @MattEvans This would delete the 1 backup we want to keep each day for up to 1 week. – cloudsafe Sep 23 '19 at 12:14
  • @Lamu Yes, but to other system processes, we cannot change our current setup. – cloudsafe Sep 23 '19 at 12:16
  • Yet you can't fix the storage problems? Why can't you change your setup l, as that would fix the problem here. – Thom A Sep 23 '19 at 12:44
  • @Larnu We have systems that look for backups and automate restoring on other machines. We would have to change app code for point in time restores. – cloudsafe Sep 23 '19 at 13:48
  • Would changing the naming convention for the once-a-day backup be feasible? – Ben Thul Sep 23 '19 at 15:52
  • @BenThul Backup names are read from the msdb system tables, so name changes are fine. All backups are full and taken every hour. we want to delete 23 out of 24 for all previous days, then all when they are over 7 days old. – cloudsafe Sep 23 '19 at 15:57
  • Sure. Take a look at the @FileName parameter to the backup solution's stored procedure. For your once-a-day backup, give it a different naming convention, set up a deletion process for both it and the once-an-hour process and you should be good to go. – Ben Thul Sep 23 '19 at 16:00

1 Answers1

0

I ended up writing a script to be run as a job at 1:30am each day. Please let me know if you can think of any problems.

declare @date as datetime
declare @execsql as nvarchar(max)
Declare mycursor cursor for 
WITH BACKUPS AS (
SELECT 
msdb.dbo.backupset.backup_start_date,  
msdb.dbo.backupmediafamily.physical_device_name, ROW_NUMBER() OVER (PARTITION BY CONVERT(CHAR(100), SERVERPROPERTY('Servername')), msdb.dbo.backupset.database_name, CAST(msdb.dbo.backupset.backup_start_date AS date) ORDER BY backup_start_date) AS RN
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
where physical_device_name like '%_MSDB%')
SELECT backup_start_date, 'xp_cmdshell ''del ' + physical_device_name + ''''
FROM BACKUPS B
WHERE backup_start_date < DATEADD(DAY,-1,CAST(GETDATE() AS DATE))
AND RN<>1
ORDER BY backup_start_date
open mycursor
fetch next from mycursor into @date, @execsql
while @@FETCH_STATUS = 0
    begin
    print @date
    print @execsql
    exec(@execsql)
    fetch next from mycursor into @date, @execsql
    end
close mycursor
deallocate mycursor
cloudsafe
  • 2,444
  • 1
  • 8
  • 24