backup and remove old files

-- i8 Solutions
-- backup database
-- retention 2 weeks


DECLARE @strDirectory NVARCHAR(50)= 'D:\i8Solutions\bu\';
DECLARE @dtDeleteBefore datetime;
DECLARE @MyFileName varchar(50);
DECLARE @strDatabaseName NVARCHAR (50)=N'i8_Portal';

SELECT @MyFileName = (SELECT  @strDirectory + @strDatabaseName + '_' + convert(varchar(50),GetDate(),112) + '.bak')

-- Declare and initialize variables
DECLARE @dt DATETIME =
ISNULL(@dtDeleteBefore, DATEADD(WEEK, -2, GETDATE()));


-- Delete the backup history older than the specified date
EXECUTE msdb.dbo.sp_delete_backuphistory
@oldest_date = @dt;

-- Delete the backup files older than the specified date
EXECUTE master.dbo.xp_delete_file
0 -- file type; 0 = backup, 1 = report
, @strDirectory -- directory in which to look for files
,  'bak'-- extension of files to look for (no period!)
, @dt -- delete files older than this date
, 1 -- traverse sub-folders; 0 = FALSE, 1 = TRUE




BACKUP DATABASE @strDatabaseName
TO  DISK = @MyFileName WITH NOFORMAT,
INIT,  NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

TSQL Last Backup Taken

select

@@SERVERNAME as ServerName,
Database_name,
COALESCE(Convert(varchar(20), MAX(backup_finish_date), 112),'Backup Not Taken') as
LastBackUpTakenDate,
COALESCE(Convert(varchar(20), MAX(user_name), 101),'NA') as BackupTakenUser
from
msdb.dbo.backupset
GROUP BY
Database_name
order by
LastBackUpTakenDate desc

Till Next Time