Count number of backup files are deleted from a default backup location

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  March 13, 2015 at 11:32 am

  #398181

  Hi Guys

  I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. Please help to modify the script...

  /* Script to delete older than N days backup from a specific directory */

  USE [db_admin]

  GO

  IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL

  DROP PROC usp_DeleteBackup

  GO

  EXEC sp_configure 'xp_cmdshell', 1

  GO

  RECONFIGURE

  GO

  /*** Running the procedure Instructions... ***/

  --EXEC usp_DeleteBackup @days = 30 -- One day old backup will be deleted

  --EXEC usp_DeleteBackup @days = 0 -- all backups will be deleted in the folder

  CREATE PROC usp_DeleteBackup

  (@days AS VARCHAR(4) = NULL -- Pass number of days

  )

  AS

  SET NOCOUNT ON

  BEGIN

  --BEGIN TRY

  DECLARE @backup_path nvarchar(2048);

  DECLARE @backupfile nvarchar(1000);

  DECLARE @BackupDirectory NVARCHAR(2048);

  --DECLARE @days AS VARCHAR(2) -- days for retention

  DECLARE @path AS VARCHAR(128) -- the path for deletion

  DECLARE @cmd AS VARCHAR(512) -- the actually command

  DECLARE @currentDateTime datetime;

  DECLARE @filename nvarchar(256);

  DECLARE @return_value INT;

  DECLARE @return_value1 INT;

  DECLARE @counter int;

  --DECLARE @min-2 INT;

  --SET @currentDateTime = GetDate();

  EXEC @return_value =

  master..xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = 'Software\Microsoft\MSSQLServer\MSSQLServer',

  @value_name = 'BackupDirectory', @BackupDirectory = @BackupDirectory OUTPUT ;

  SET @backup_path = (SELECT @BackupDirectory AS [SQL Server default backup Value])

  --select @backup_path

  --SELECT @backup_path

  --SELECT @return_value;

  IF (@return_value <> 0) -- It's a failure

  PRINT 'Unable to retrieve a valid Backup directory from Registry'

  ELSE

  BEGIN

  --SET @days = '3' -- change the days here, remember it is type VARCHAR

  SET @days = @days;

  SET @cmd = 'forfiles /P "' + @backup_path + '" /S /M *.bak /D -' + @days + ' /C "cmd /c del @PATH"'

  --Print @cmd

  EXEC @return_value1 = master.dbo.xp_cmdshell @cmd

  PRINT @return_value1

  IF (@return_value1 =0)

  --SELECT @@ROWCOUNT AS DELETED;

  PRINT 'Backup Files are successfully dropped'

  ELSE

  RETURN @@error

  SET NOCOUNT OFF

  END

  END

  Thanks.

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  March 14, 2015 at 1:47 am

  #1784024

  Quick suggestion, run a DIR before and after, the difference in the counts are the number of files deleted

  😎

• Jeff Moden

  SSC Guru

  Points: 1004641

  More actions

  March 14, 2015 at 10:17 am

  #1784034

  To be honest, I wouldn't use the DOS "loop" to do the deletes. I'd import the results of a DIR into a table and parse the row information and then use that to guide the deletes. My reasoning for doing such a thing is so that you can not only do the count(s) you're talking about but also so you can do a little reporting on the names, sizes, and dates of files that were deleted, those that remain, and to do a little error handling in case you try to delete a file that's currently open.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)