Take SQL Backup Using SP

One of these tasks may be the need to backup all databases on your server. This is not a big deal if you have a handful of databases, but using T-SQL is a much simpler and faster approach.

This is a script to take a sql database backup
NOTE: Plaese make a folder on "d:\SqlDataBaseBackupD"

CREATE procedure [dbo].[sp_DBBackupD]
as

declare @Sdate varchar(250)
DECLARE @SERVER VARCHAR(100), @COMMAND VARCHAR(100), @FILE VARCHAR(100)

set @Sdate= replace(convert(varchar,GETDATE(),103),'/','')+replace(convert(varchar,GETDATE(),108),':','')+'_TEST.BAK'
set @FILE= replace(convert(varchar,GETDATE(),103),'/','')+replace(convert(varchar,GETDATE(),108),':','')+'_TEST.RAR'

set @Sdate ='d:\SqlDataBaseBackupD\' + @Sdate

BACKUP DATABASE TEST

TO DISK = @Sdate
   WITH FORMAT
PRINT @Sdate + ' Backup file is ready'
 

SET @COMMAND = 'D:\SqlDataBaseBackupD\RAR.exe a D:\SqlDataBaseBackupD\' + @FILE + ' '+@Sdate
--EXEC XP_CMDSHELL @COMMAND
PRINT @FILE + ': Backup file is COMPRESSED'

-- EXCLUIR FILE BACKUP
--SET @COMMAND ='DEL '+ @Sdate
--EXEC XP_CMDSHELL @COMMAND
--PRINT 'Delete temporary file'
PRINT 'Download Compressed Backup file '+ @file