CREATE PROCEDURE usp_backup
@path VARCHAR(400), -- Include \ finally. Eg. 'D:\'
@retentionperiod TINYINT -- retention period in days
AS
SET NOCOUNT ON
DECLARE @date VARCHAR(100)
DECLARE @dbname VARCHAR(50)
DECLARE @bkup VARCHAR(500)
DECLARE @retention VARCHAR(500)
--Server Details
PRINT '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$'
PRINT '$ SERVERNAME: '+CONVERT(VARCHAR(50),SERVERPROPERTY('SERVERNAME'))+' — DATE: '+ CONVERT(VARCHAR(50),GETDATE()) + ' $'
PRINT '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$'
PRINT ''
--Retention
SET @date= CONVERT(VARCHAR(10),getdate()-@retentionperiod,112)
SET @retention='EXEC master.dbo.xp_cmdshell ''dir /b ' +@path+'*'+@date+'*.bak'''
PRINT 'Retention'
PRINT '^^^^^^^^^'
PRINT 'Deleting the below backup files as part of retention plan...'
EXEC (@retention)
SET @retention='EXEC master.dbo.xp_cmdshell ''del /Q ' +@path+'*'+@date+'*.bak'''
EXEC (@retention)
PRINT''
PRINT'Database Backups Started'
PRINT'^^^^^^^^^^^^^^^^^^^^^^^^'
--Backup Script
SET @date= CONVERT(VARCHAR(10),getdate(),112)
DECLARE bkup_cursor CURSOR FOR SELECT NAME FROM master.dbo.sysdatabases WHERE dbid <> 2
OPEN bkup_cursor 
FETCH NEXT FROM bkup_cursor INTO @dbname
IF @@FETCH_STATUS <> 0 
PRINT 'No database to backup...!!'
WHILE @@FETCH_STATUS = 0
BEGIN
SET @bkup='BACKUP DATABASE '+@dbname+' TO DISK = '''+@path+@dbname+'_'+@date+'.bak'' WITH INIT'
PRINT '************Processing '+@dbname+' Backup... **************'
EXEC (@bkup)
PRINT 'Backed up to ' + @path+@dbname+'_'+@date+'.bak'
PRINT '********************************************************'
PRINT ''
FETCH NEXT FROM bkup_cursor INTO @dbname
END
CLOSE bkup_cursor
DEALLOCATE bkup_cursor
PRINT '============Backup Completed Successfully============'