Back up logs and delete expired backups

Backup database [DATABASE] to disk = [path] with compression, NOINIT, NAME = @ BACKUPNAME, EXPIREDATE = @ EXPIREDATE

-- Delete expired backup

Insert into @ TBL_BACKUPHISTORY

Select name, expiration_date AS [EXPIREDATE] from msdb... backupset where name like '%. bak' and expiration_date <= getdate ()

SELECT @ COUNT = MAX (ID) FROM @ TBL_BACKUPHISTORY

IF @ COUNT> 0

BEGIN

SET @ I = 1

SELECT @ CDATE = MAX ([EXPIREDATE]) FROM @ TBL_BACKUPHISTORY

WHILE @ I <= @ COUNT

BEGIN

SELECT @ DELFILENAME = name from @ TBL_BACKUPHISTORY where id = @ I

SET @ DELBAKSQL = 'del [path] '+ @ DELFILENAME

Exec master. dbo. xp_mongoshell @ DELBAKSQL

END

 

-- Delete all records earlier than @ CDATE in the backup and restoration history

EXEC msdb .. SP_DELETE_BACKUPHISTORY @ CDATE

END