[TransactSQL] Backup website and database from SQL job (uses winrar)

Backup app from sql job

1. Map network drive

EXEC XP_CMDSHELL 'net use W: \\mainstore\Backup PASSWORD /user:USERNAME'

2. Backup Database

declare
     @timestamp varchar(8),
     @fileName varchar(1024)

SELECT @timestamp = CONVERT(VARCHAR(8), GETDATE(), 112)

select @fileName =  N'W:\APPS\Databases\DBNAME_' + @timestamp +'.bak'


BACKUP DATABASE DBNAME
TO DISK =@filename

3. Backup Site

EXEC XP_CMDSHELL '"C:\Program Files\WinRAR\Rar.exe" a -ag W:\APPS\WebSites\SITENAME_.rar I:\WebSites\SITENAME'

4. Disconnect Network Drive

EXEC XP_CMDSHELL 'net use W: /DELETE'