Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

SQL 2008 Backup Removal

SQL 2008

Is there a way I can delete old backups stored on my local hard drive.

I currently have a folder on the server that I backup to.   I then back this folder up over night.
I want to have say a weeks worth of the backup files on the hard drive and then remove anything over 7 days.

Any advise?
0
Bransby-IT
Asked:
Bransby-IT
  • 2
  • 2
1 Solution
 
UnifiedISCommented:
You can add a cleanup task to your maintenance plan that will delete old backups
0
 
lcohanDatabase AnalystCommented:
--The code below does just that and all you need is to addapt it to your folder/file names. I suggest use the PRINT commands until you get exactly what you need.

--delete old weeky bkps

---check if zip file exists on C:\SQL_BACKUP then delete old files from G:
set nocount on
declare @crtdate varchar(255)
declare @sqlcmd varchar(4000)

set  @crtdate = CAST(YEAR(getdate()) AS VARCHAR(4))+ '_' +
case when MONTH(getdate()) < 10
      then '0'+CAST(MONTH(getdate()) AS VARCHAR(2))
      else CAST(MONTH(getdate()) AS VARCHAR(2))
end + '_' +
case when DAY(getdate()) < 10
      then '0'+CAST(DAY(getdate()) AS VARCHAR(2))
      else CAST(DAY(getdate()) AS VARCHAR(2))
end
--print @crtdate

--test command
--set @sqlcmd = 'EXEC xp_cmdshell ''dir "C:\SQL_BACKUP\SQL_BACKUP_'+@crtdate+'.zip"'''
--print (@sqlcmd)

create table #t (c1 text)
set @sqlcmd = 'insert into #t exec xp_cmdshell ''dir I:\SQL_BACKUP\SQL_BACKUP_'+@crtdate+'.zip'''
--print (@sqlcmd)
exec (@sqlcmd)
--select * from #t
--select * from #t where c1 like '%.zip'

if exists (select * from #t where c1 like '%.zip')
begin
      -- delete all old backup files from G:\sql_backup directory
      declare @source varchar(255)

      set @source = '*_backup_' + CAST(YEAR(getdate()-7) AS VARCHAR(4))+ '_' +
      case when MONTH(getdate()-7) < 10
            then '0'+CAST(MONTH(getdate()-7) AS VARCHAR(2))
            else CAST(MONTH(getdate()-7) AS VARCHAR(2))
      end + '_' +
      case when DAY(getdate()-7) < 10
            then '0'+CAST(DAY(getdate()-7) AS VARCHAR(2))
            else CAST(DAY(getdate()-7) AS VARCHAR(2))
      end
      +'*.bak'''

      --test command
      set @sqlcmd = 'EXEC xp_cmdshell ''dir G:\SQL_BACKUP\'+@source
      exec (@sqlcmd)
      --print @sqlcmd
            
      set @sqlcmd = 'EXEC xp_cmdshell ''del G:\SQL_BACKUP\'+@source
      exec (@sqlcmd)
      --print @sqlcmd

end
drop table #t

GO
0
 
Bransby-ITAuthor Commented:
HI Team

I thought the Clean up would not actualy remove old backup files?

Also I cant make heads of the script lol, this is what I would like to do.

2 x Backup Locations

E:\SQL2008\Access_Backups\Backup_SQL_Database_1300Hrs
E:\SQL2008\Access_Backups\Backup_SQL_Database_1800Hrs
All backup files are in there own sub folder

Filenames

.bak

want to keep backups for 7 days
0
 
UnifiedISCommented:
backup-cleanup.docx
I have mine set for 2 weeks, see the attachment.  It is definitely deleting the old .bak files
0
 
Bransby-ITAuthor Commented:
Thanks will look at this this afternon.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now