SQL database backup and remove files automatically

DECLARE @name VARCHAR(50) -- database name  

DECLARE @path VARCHAR(256) -- path for backup files  

DECLARE @fileName VARCHAR(256) -- filename for backup  

DECLARE @fileDate VARCHAR(20) -- used for file name  

DECLARE @DeleteBakDate DATETIME = Dateadd(day, -5, Getdate()); -- 5-day retention for .bak files

DECLARE @DeleteTxtDate DATETIME = Dateadd(day, -30, Getdate()); -- 30-day retention for .txt files

DECLARE @outputFile VARCHAR(256) -- output log file

-- specify database backup directory

SET @path = '\\servername\foldername\'

-- specify filename format for backup

SELECT @fileDate = CONVERT(VARCHAR(20), Getdate(), 112) + '_' + Replace(CONVERT(VARCHAR(20), Getdate(), 108), ':', '')

-- specify output file for job with date format

SET @outputFile = @path + 'Backup_Job_Output_' + @fileDate + '.txt'

DECLARE db_cursor CURSOR read_only FOR  

  SELECT NAME  

  FROM master.dbo.sysdatabases  

  WHERE NAME NOT IN ('master', 'model', 'msdb', 'tempdb') -- exclude these databases

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  

  BEGIN  

      SET @fileName = @path + @name + '_' + @fileDate + '.bak'  

      

      -- Backup database and capture the output using xp_cmdshell

      DECLARE @cmd VARCHAR(1024)

      SET @cmd = 'ECHO Backing up database ' + @name + ' to ' + @fileName + ' >> ' + @outputFile

      EXEC xp_cmdshell @cmd

      BACKUP DATABASE @name TO DISK = @fileName  

      WITH copy_only, noformat, noinit, skip, norewind, nounload, compression,

           STATS = 10

      -- Log the backup operation result to the output file

      SET @cmd = 'ECHO Backup of database ' + @name + ' completed successfully to ' + @fileName + ' >> ' + @outputFile

      EXEC xp_cmdshell @cmd

      

      FETCH NEXT FROM db_cursor INTO @name  

  END  

-- Delete old .bak files (older than 5 days)

EXEC master.sys.Xp_delete_file 0, @path, 'BAK', @DeleteBakDate, 0

-- Delete old .txt files (older than 30 days)

EXEC master.sys.Xp_delete_file 0, @path, 'TXT', @DeleteTxtDate, 0

CLOSE db_cursor  

DEALLOCATE db_cursor  

-- Final log entry

DECLARE @cmdFinal VARCHAR(1024)

SET @cmdFinal = 'ECHO Backup process completed at ' + CONVERT(VARCHAR(20), GETDATE(), 120) + ' >> ' + @outputFile

EXEC xp_cmdshell @cmdFinal