Create zip file of backup and delete old backup files - sql server

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[7ZipBackupProcess]    Script Date: 7/26/2018 3:40:16 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[7ZipBackupProcess]

AS

BEGIN


/*
##Script to Compress the backups in sql express using 7 zip
##Author: Akhil
##Date:09/19/2016
##Rev:1 initial Setup
##Rev:2 fix the date add to negitive
##Rev:3 Remove xp_delete_file instead use batch command to remove old files.
*/

--parameter
declare @myPath varchar(4000) =  'E:\MSSQL-Backup';

declare @apath varchar(1000) = '';
declare @aextn varchar(10)= '7z';
declare @7zexe varchar(1000)= 'C:\Program Files\7-Zip\7z.exe'
declare @cleanupdays int = 7;
declare @cleanupdate datetime;

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
--,filename
,depth int
,isfile bit
, ParentDirectory int
,flag tinyint default(0));

-- top level directory
INSERT #DirectoryTree (subdirectory,depth,isfile)
VALUES (@myPath,0,0);
-- all the rest under top level
INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree @myPath,0,1;


UPDATE #DirectoryTree
SET ParentDirectory = (
SELECT MAX(Id) FROM #DirectoryTree
WHERE Depth = d.Depth - 1 AND Id < d.Id )
FROM #DirectoryTree d;

-- SEE all with full paths
declare @archivefiles cursor ;
declare @bfile sysname;
declare @afile sysname;
declare @conataner sysname;
declare @result int;
declare @cmd varchar(8000);

--Get all the files

set @archivefiles = cursor for
WITH dirs AS (
SELECT
Id,subdirectory,depth,isfile,ParentDirectory,flag
, CAST (null AS NVARCHAR(MAX)) AS container
, CAST([subdirectory] AS NVARCHAR(MAX)) AS dpath
FROM #DirectoryTree
WHERE ParentDirectory IS NULL
UNION ALL
SELECT
d.Id,d.subdirectory,d.depth,d.isfile,d.ParentDirectory,d.flag
, dpath as container
, dpath +'\'+d.[subdirectory] 
FROM #DirectoryTree AS d
INNER JOIN dirs ON  d.ParentDirectory = dirs.id
)
SELECT dpath,case when @apath is null or len(@apath) = 0 then replace(dpath,'.bak','.'+@aextn)
 else @apath+replace(subdirectory,'.bak','.'+@aextn)  end as afile
    FROM dirs
-- Dir style ordering
where isfile = 1 and subdirectory like '%.bak'


print('Archive Process')

open @archivefiles

fetch next from @archivefiles into @bfile,@afile
while @@FETCH_STATUS = 0
begin
print (@bfile)
print (@afile)
set @cmd = '""'+@7zexe+'" a "' + @afile+'" "'++ @bfile+'"'+' -sdel'+'"'

print('Archive Command'+@cmd)
exec @result =  xp_cmdshell @cmd
print(@result)
fetch next from @archivefiles into @bfile,@afile
end

print('Clean up')

set @cmd = '"forfiles -p "'+@myPath+'" -s -m *.'+@aextn+' /D -'+convert(varchar,@cleanupdays)+' /C "cmd /c del @path"'
print('Delete Command '+@cmd)
exec @result =  xp_cmdshell @cmd
print(@result)

End