Delete backup files older than 7 days

Question

Using osql command, SSQL backup of a database is created. It is saved to Disk. Then renamed to match the date of the day backup was taken. All these files are saved in a single folder all the time.

for example: Batch1.bat does the following 1) Created backup.bak 2) renamed to backup 12-13-2009.bak (this is done by the combination of % ~ - etc to get the date parameter)

This is now automated to take backup everyday by Task scheduler in Windows.

Can the batch file also be modified to delete backup files older than 7 days? if so, how ?

If it is not possible via batch file, other than manually deleting the files are there any other alternatives to automate the deleting job ?

Thanks in advance, Balaji S

Answer 1

I'm using the same technique to make a backup from database. I've created a stored procedure as follows.

Create Procedure [dbo].[CreateBackup]
As
Begin

 Declare @path nvarchar(256),
            @filename nvarchar(256),
   @currentDateTime datetime

 Set @currentDateTime = GetDate()
 Set @path = 'C:\DBBackup\'
 Set @filename = @path + Cast(DatePart(month, @currentDateTime) As nvarchar) + Cast(DatePart(day, @currentDateTime) As nvarchar) + '.bak'

 Backup Database Foo To Disk = @filename

 Set @currentDateTime = DateAdd(day, -3, @currentDateTime)
 Set @filename = 'del ' + @path + Cast(DatePart(month, @currentDateTime) As nvarchar) + Cast(DatePart(day, @currentDateTime) As nvarchar) + '.bak'

 Exec xp_cmdshell @filename

End

To use the xp_cmdshell, you should enable it before.

http://weblogs.sqlteam.com/tarad/archive/2006/09/14/12103.aspx

Answer 2

enter code hereThis procedure backup given database(s) for specified @retentionPeriod to a given network location (or local)

    USE [master]
GO
/****** Object:  StoredProcedure [dbo].[scrDoRemoteBackup]    Script Date: 12/13/2009 09:20:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER                proc [dbo].[scrDoRemoteBackup] as
set NoCount on
declare @dbName Varchar(100)
   ,@fName varchar(100)
   ,@RetentionPeriod Int -- in days 
   ,@i int
   ,@n int
   ,@NetPath varchar(400)
   ,@BackupSetName Varchar(300)
   ,@params nvarchar(300)
   ,@q nvarchar(3400)
   ,@cmd nvarchar(4000)
   ,@error int
Declare @DataBases2Backup table(id int identity(1,1),dbName Varchar(100))

-- total back time 4:24 2nd Nov 06
set @NetPath='\\rapidsnet01s\RapidsDbBackup'
set @netPath='\\mail1\RapidsDbBackup'
set @netPath = '\\rapidsweb02p\Backup'
set @netPath='\\BUP2D-2K\_RapidsDB$\SAABZXO1D' -- 26th Feb 2008 - ap/li rapidsweb02p\backup space runout
set @netPath='\\saacsfs\RapidsBackup\SAABZXo1d' -- 22nd Oct 2009
---- insert all databaes to be backed up
--Insert @DataBases2Backup select 'Rapids'
--Insert @DataBases2Backup select 'GDC'
--Insert @DataBases2Backup select 'Master'
--
--
----Insert @DataBases2Backup select 'GDCComDev'
--
--Insert @DataBases2Backup select 'saaNCP'
----Insert @DataBases2Backup select 'US_Reps'
--Insert @DataBases2Backup select 'saaPackageWorx'
--Insert @DataBases2Backup select 'saaExtract'
Insert @DataBases2Backup select 'Master'
Insert @DataBases2Backup select 'QuickPickDBprod'
Insert @DataBases2Backup select 'QuickPickDBStaging'


--Set @RetentionPeriod = 13
Set @RetentionPeriod = 6  -- For Terry McCraith   Jan06'09
select @n= count(*) from @DataBases2Backup
set @i = 1;

-- Set the Network path for the Backup location ; 
-- ( re-establish the connection if the connection was broken)
set @q = 'net use '+@Netpath --+' * /USER:[email protected]'
print @q
exec master.dbo.xp_cmdShell @q

While @i <= @n
 Begin
  select @dbName=dbName from @DataBases2Backup where id = @i
  -- Get the backupset name prior to Retention Period 
  set @BackupSetName=@dbName+ dbo.fnGetDateNameBefore(GetDate(),@RetentionPeriod)
  -- Delete the old backup device
  set @q='Del '+@NetPath+'\'+@BackupSetName+'.bkp'
  exec master.dbo.xp_cmdShell @q
  -- Now get the current backupset name and backit up
  set @BackupSetName=@dbName+ dbo.fnGetDateNameBefore(GetDate(),0)
   set @fname = @netPath +'\'+@BackupSetName+'.bkp'
  print 'Fname ='+@fname
  Print 'Drop and Add Dumpdevice ' + @dbname + ' Fname='+@fname
  exec @error=sp_dropDevice @dbname  
  print 'Error drop device-----------'+Convert(varchar,@error)+':'+Convert(varchar,@@error)

  exec @error=sp_addumpDevice 'disk',@dbName,@fname 
  exec sp_helpdevice @dbName
  print 'Error -----------'+Convert(varchar,@error)
  set @i=@i+1
  if @error <> 0
     Begin
   print 'send alert'
      exec  saabzt01p.alerts.dbo.prDispatchAlertV2 'RemoteDBBackupError' ,@@servername,'test','RemoteDBBackupError','test'

     End
  else
      Begin
    Backup Log @dbname with truncate_only
    Backup Database @dbname to @dbname with format
   if @@Error <> 0
   Begin
    print 'Send alert'
    exec  saabzt01p.alerts.dbo.prDispatchAlertV2 'RemoteDBBackupError',@@servername,'test','RemoteDBBackupError','test'
        Goto Errors
   end
      end 
 End

Errors:

And here are supporting functions

-- This function returns oldest data set name compared to retentionPeriod
Create     function [dbo].[fnGetDateNameBefore](@d Datetime,@nDays int) returns varchar(40) as
Begin
 declare @OD datetime
    ,@dName Varchar(40);

 set @OD=DateAdd(dd,@nDays * -1,@d)

 select @dName= DateName(yy,@od)+Left(DateName(Month,@od),3)+DateName(dd,@od)
 Return @dName
end

-- This function returns oldest data set name compared to retentionPeriod Create function [dbo].fnGetDateNameBefore returns varchar(40) as Begin declare @OD datetime ,@dName Varchar(40);

set @OD=DateAdd(dd,@nDays * -1,@d)

select @dName= DateName(yy,@od)+Left(DateName(Month,@od),3)+DateName(dd,@od) Return @dName end

Answer 3

If you have RoboCopy (part of the Windows Server 2003 Resource Kit Tools) installed.

The following lines can be added to your Batch1.bat file to move and delete files older than seven days:

ROBOCOPY C:\Temp\New C:\Temp\Old *.* /move /minage:7
DEL C:\Temp\Old\*.*

The first row moves all files that are older than seven days in the 'New' folder to the 'Old' folder.

The second row deletes all files in the 'Old' folder

Answer 4

You can get all of this functionality out of the box in 2 minutes with SQLBackupAndFTP. It is free for 2 databases and $49 for unlimited.