SQL Server ERP Backup Script Generator: Full, Differential and Log Backups

DECLARE @stamp varchar(20) = REPLACE(REPLACE(REPLACE(CONVERT(varchar(19), GETDATE(), 120), '-', ''), ':', ''), ' ', '_');
DECLARE @file nvarchar(400) = N'D:\Backups\ERP_PROD\ERP_PROD_FULL_' + @stamp + N'.bak';

BACKUP DATABASE [ERP_PROD]
TO DISK = @file
WITH COMPRESSION, CHECKSUM, INIT, FORMAT, STATS = 10,
     NAME = N'ERP_PROD full backup';

RESTORE VERIFYONLY FROM DISK = @file WITH CHECKSUM;

DECLARE @stamp varchar(20) = REPLACE(REPLACE(REPLACE(CONVERT(varchar(19), GETDATE(), 120), '-', ''), ':', ''), ' ', '_');
DECLARE @file nvarchar(400) = N'D:\Backups\ERP_PROD\ERP_PROD_DIFF_' + @stamp + N'.bak';

BACKUP DATABASE [ERP_PROD]
TO DISK = @file
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM, INIT, STATS = 10,
     NAME = N'ERP_PROD differential backup';

DECLARE @stamp varchar(20) = REPLACE(REPLACE(REPLACE(CONVERT(varchar(19), GETDATE(), 120), '-', ''), ':', ''), ' ', '_');
DECLARE @file nvarchar(400) = N'D:\Backups\ERP_PROD\ERP_PROD_LOG_' + @stamp + N'.trn';

BACKUP LOG [ERP_PROD]
TO DISK = @file
WITH COMPRESSION, CHECKSUM, INIT, STATS = 10,
     NAME = N'ERP_PROD log backup';

SELECT d.name, d.recovery_model_desc,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name
WHERE d.name = N'ERP_PROD'
GROUP BY d.name, d.recovery_model_desc;

DECLARE @cutoff datetime = DATEADD(DAY, -35, GETDATE());
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @cutoff;

-- Backup files themselves are not removed by this call.
-- Delete old .bak and .trn files with a file system job or your backup tool.