Take all database backups using single script

 Try this script 

======

DECLARE @BackupPath NVARCHAR(500) = 'D:\Backups\' -- Change this to your desired path

DECLARE @BackupRetentionDays INT = 10

DECLARE @LogRetentionDays INT = 30

DECLARE @CurrentDate DATETIME = GETDATE()

DECLARE @LogPath NVARCHAR(500) = @BackupPath + 'Logs\'

DECLARE @FileName NVARCHAR(500)

DECLARE @DatabaseName NVARCHAR(128)

DECLARE @LogMessage NVARCHAR(MAX)

DECLARE @BackupDeleteDate DATETIME = DATEADD(DAY, -@BackupRetentionDays, @CurrentDate)

DECLARE @LogDeleteDate DATETIME = DATEADD(DAY, -@LogRetentionDays, @CurrentDate)

-- Create directories if they don't exist

DECLARE @CreateDirSQL NVARCHAR(1000)

SET @CreateDirSQL = 'xp_cmdshell ''if not exist "' + @BackupPath + '" mkdir "' + @BackupPath + '"'''

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE

EXEC(@CreateDirSQL)

EXEC('xp_cmdshell ''if not exist "' + @LogPath + '" mkdir "' + @LogPath + '"''')

-- Log file setup

DECLARE @LogFile NVARCHAR(500) = @LogPath + 'BackupLog_' + 

                                REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), @CurrentDate, 120), ':', ''), '-', ''), ' ', '_') + '.txt'

-- Initialize log

SET @LogMessage = 'Backup process started at: ' + CONVERT(NVARCHAR(30), @CurrentDate, 120) + CHAR(13) + CHAR(10)

EXEC master.dbo.xp_cmdshell 'echo %LogMessage% > "' + @LogFile + '"', no_output

-- Cursor to iterate through all user databases

DECLARE db_cursor CURSOR FOR 

SELECT name 

FROM sys.databases 

WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') 

AND state = 0 -- Online databases only

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

    BEGIN TRY

        -- Generate backup file name

        SET @FileName = @BackupPath + @DatabaseName + '_Full_' + 

                       REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), @CurrentDate, 120), ':', ''), '-', ''), ' ', '_') + '.bak'

        

        -- Backup command

        BACKUP DATABASE @DatabaseName 

        TO DISK = @FileName

        WITH INIT, STATS = 5, COMPRESSION

        

        -- Log success

        SET @LogMessage = 'SUCCESS: Backup completed for database: ' + @DatabaseName + ' at ' + CONVERT(NVARCHAR(30), GETDATE(), 120) + CHAR(13) + CHAR(10)

        EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

        

    END TRY

    BEGIN CATCH

        -- Log error

        SET @LogMessage = 'ERROR: Failed to backup database: ' + @DatabaseName + ' - ' + ERROR_MESSAGE() + ' at ' + CONVERT(NVARCHAR(30), GETDATE(), 120) + CHAR(13) + CHAR(10)

        EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

    END CATCH

    

    FETCH NEXT FROM db_cursor INTO @DatabaseName

END

CLOSE db_cursor

DEALLOCATE db_cursor

-- Cleanup old backup files (older than backup retention period)

BEGIN TRY

    DECLARE @DeleteBackupCmd NVARCHAR(1000)

    SET @DeleteBackupCmd = 'forfiles /p "' + @BackupPath + '" /s /m *.bak /d -' + CAST(@BackupRetentionDays AS NVARCHAR(3)) + ' /c "cmd /c del /q @path"'

    EXEC xp_cmdshell @DeleteBackupCmd

    

    SET @LogMessage = 'Cleanup: Old backup files deleted (older than ' + CAST(@BackupRetentionDays AS NVARCHAR(3)) + ' days)' + CHAR(13) + CHAR(10)

    EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

END TRY

BEGIN CATCH

    SET @LogMessage = 'ERROR during backup cleanup: ' + ERROR_MESSAGE() + CHAR(13) + CHAR(10)

    EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

END CATCH

-- Cleanup old log files (older than log retention period)

BEGIN TRY

    DECLARE @DeleteLogCmd NVARCHAR(1000)

    SET @DeleteLogCmd = 'forfiles /p "' + @LogPath + '" /s /m *.txt /d -' + CAST(@LogRetentionDays AS NVARCHAR(3)) + ' /c "cmd /c del /q @path"'

    EXEC xp_cmdshell @DeleteLogCmd

    

    SET @LogMessage = 'Cleanup: Old log files deleted (older than ' + CAST(@LogRetentionDays AS NVARCHAR(3)) + ' days)' + CHAR(13) + CHAR(10)

    EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

END TRY

BEGIN CATCH

    SET @LogMessage = 'ERROR during log cleanup: ' + ERROR_MESSAGE() + CHAR(13) + CHAR(10)

    EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

END CATCH

-- Final log entry

SET @LogMessage = 'Backup process completed at: ' + CONVERT(NVARCHAR(30), GETDATE(), 120) + CHAR(13) + CHAR(10)

EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

-- Reset xp_cmdshell for security

EXEC sp_configure 'xp_cmdshell', 0

RECONFIGURE

EXEC sp_configure 'show advanced options', 0

RECONFIGURE

PRINT 'Backup process completed. Check log file at: ' + @LogFile

======