Full and Differential Backups in a fashion

One day, I got a requirement for the new server backup strategy.. Of course I have done this before but didn't carry the scripts with me as I always feels I can implement on the fly..
But this time I just want to save this script and may useful for others.
Requirement : 
               Full backup has to run every 'SUNDAY' and Differentials  should have to run every day.
Everything in the same script...
 The reason why I did both in the same script is to not fail the job on any weekday as the existing database refreshed or new database created.
Reason to fail: As the 'new database' / 'refreshed' haven't had the Full backup yet, The direct diff. backups would be fails.

So,  this script will check for the full backup on every day for every database.

    DECLARE @DBName VARCHAR(40)
    DECLARE @SQL VARCHAR(max)

    DECLARE DataBaseName CURSOR FOR
            SELECT s.name  FROM sys.databases s where s.database_Id > 4
             --s.name IN ('MyDB1','MyDB2') -- You can name your own databases here
   
    OPEN DataBaseName

    FETCH NEXT FROM DataBaseName 
    INTO @DBName

    WHILE @@FETCH_STATUS = 0
    BEGIN

    DECLARE @BackName VARCHAR(50) = @DBName +'_'+REPLACE(CONVERT(VARCHAR(10),GETDATE(),101),'/','') -- I want this format MyDB1_MMDDYYYY
    DECLARE @BackPath VARCHAR(500)
   

    IF (DATENAME (DW,GETDATE()) = 'SUNDAY'
                        OR      -- Will check whether the full backup happend at all on the database
                        (SELECT ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup
                        FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
                        ON A.database_name = B.name AND A.type = 'D'
                        WHERE B.name  = @DBName
                        ) = 'NEVER'
                        )
       
        BEGIN
            SET  @BackPath = 'E:\FullBackup\'+ @BackName

            SET @SQL = 'BACKUP DATABASE ['+@DBName+'] TO  DISK = N'''+@BackPath+'.bak'' WITH NOFORMAT, NOINIT,  NAME = N'''+@DBName+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
           
            declare @backupSetId as int
            select @backupSetId = position from msdb..backupset where database_name=N'''+@DBName+''' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'''+@DBName+''' )
            if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database '''''+@DBName+''''' not found.'', 16, 1) end
            RESTORE VERIFYONLY FROM  DISK = N'''+@BackPath+'.bak'' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND'
           

            EXEC (@SQL)

        END
    ELSE
        BEGIN
           
            --If(DB <> FullBackup)
            --    take full backup
            SET  @BackPath = 'E:\DifferentialBackup\'+ @BackName
            SET @SQL = 'BACKUP DATABASE ['+@DBName+'] TO  DISK = N'''+@BackPath+'_Diff.Bak'' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'''+@DBName+'-Differential Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
           
            declare @backupSetId as int
            select @backupSetId = position from msdb..backupset where database_name=N'''+@DBName+''' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'''+@DBName+''' )
            if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database '''''+@DBName+''''' not found.'', 16, 1) end
            RESTORE VERIFYONLY FROM  DISK = N'''+@BackPath+'_Diff.Bak'' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND'
           

            EXEC (@SQL)
        END
    FETCH NEXT FROM DataBaseName INTO @DBName

    END
    CLOSE DataBaseName;
    DEALLOCATE DataBaseName;

    GO



Now, the real problem is space.... The backups keep on going We are not overwriting / Deleting yet.. So, the below script may useful for the automation. Otherwise you have do delete the files manually...
You can attach the following script with the above script..


IF (DATENAME (DW,GETDATE()) = 'SUNDAY' )
    BEGIN
             --Only sunday We can delete the old FullBackup files...
            DECLARE @DeleteDateFull DATETIME
            SET @DeleteDateFull = DateAdd(day, -15, GetDate())  -- Its your setting the number of days
            EXECUTE master.sys.xp_delete_file
            0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
            N'E:\FullBackup\', -- folder path (trailing slash)
            N'bak', -- file extension which needs to be deleted (no dot)
            @DeleteDateFull, -- date prior which to delete
            1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)

                 --Only sunday We can delete the old Differential backup files...
            DECLARE @DeleteDateDiff DATETIME
            SET @DeleteDateDiff = DateAdd(day, -15, GetDate()) -- Its your setting the number of days
            EXECUTE master.sys.xp_delete_file
            0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
            N'E:\DifferentialBackup\', -- folder path (trailing slash)
            N'bak', -- file extension which needs to be deleted (no dot)
            @DeleteDateDiff, -- date prior which to delete
            1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)
    END