Backup task is something that is very much required to be done every day in order to maintain working system over the time and recover through potential system crashes. However scheduling the one is not a great task, but needs accuracy in execution
Through my observations of the processes I would not recommend third party tools performing backup tasks as they don’t work well for large database sometimes. Also taking the task away from SQL means inviting more security concerns to the data. SSIS package is one of the best ways to go for this task. You can design backup tasks and as the process runs on SQL server it saves time without hampering the security concerns. Another way to do this is through queries. I have noted down some steps below that you can add to SQL job and perform backup tasks seamlessly.
These methods make use of xp_cmdshell command and hence they need to be enabled first in SQL. For that you can use below command.
— To allow advanced options to be changed.
EXEC sp_configure ‘show advanced options’, 1
GO
— To update the currently configured value for advanced options.
RECONFIGURE
— To enable the feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
— To update the currently configured value for this feature.
RECONFIGURE
GO
Then you can create WINRAR compressed file for the backup file by running below mentioned commands
— BACKUP
BACKUP DATABASE [DARS] TO DISK = N’C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupCRM.BAK’
— GENERATE RAR FILE
DECLARE @SERVER VARCHAR (100), @COMMAND VARCHAR (100), @FILE VARCHAR(100)
SET @SERVER = @@SERVERNAME
SET @COMMAND = ‘”C:Program FilesWinRARRAR.exe” A “C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupCRM.BAK”‘
EXEC XP_CMDSHELL @COMMAND
— DELETE BACKUP FILE
EXEC XP_CMDSHELL ‘DEL “C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupCRM.BAK”‘