SQL Server - Daily Backups

dailyBackups.sql

BEGIN

    SELECT [name], [recovery_model_desc], sys.fn_hadr_is_primary_replica([name]) as [pr] FROM sys.databases

    DECLARE @db VARCHAR(128)

    DECLARE @targets TABLE (db varchar(128))
    INSERT INTO @targets (db) VALUES ('MyDB'), ('YourDB')
    DECLARE c CURSOR FOR
    SELECT [db] FROM @targets
 
    OPEN c
    FETCH NEXT FROM c INTO @db
    print '@db = ' + @db

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @full VARCHAR(9) = 'Saturday'
        DECLARE @path VARCHAR(128) = 'K:\MSSQL\Backup\daily\'
      
        DECLARE @today VARCHAR(9) = datename(w,sysdatetime())
        DECLARE @type VARCHAR(4) =  CASE @today WHEN @full THEN 'FULL' ELSE 'DIFF' END
        DECLARE @option VARCHAR(128) = CASE @today WHEN @full THEN 'COMPRESSION' ELSE 'DIFFERENTIAL, COMPRESSION' END
      
        DECLARE @filename VARCHAR(128) = @path
             + @db
             + '_'
             + @type
             + '_'
             + convert(varchar,sysdatetime(), 112)
             + '_'
             + substring(replace(convert(varchar,sysdatetime(), 114),':',''), 1, 6)
             + '.bak'
      
        DECLARE @sql NVARCHAR(512) = 'BACKUP DATABASE ' + @db + ' TO DISK = ''' + @filename + ''' WITH ' + @option

        print '@sql = ' + @sql
        EXECUTE sp_executesql @sql
 
        DECLARE @retention DATETIME = DATEADD(wk,-2,GETUTCDATE())
        print '@retention = ' + cast(@retention as varchar(max))
        EXEC xp_delete_file 0, @path, 'bak', @retention, 0

        FETCH NEXT FROM c INTO @db
        print '@db = ' + @db
        print '@@fetch_status = ' + cast(@@fetch_status as varchar(max))
    END
    CLOSE c
    DEALLOCATE c
END