@ECHO OFF
REM Backing up multiple MSSQL Databases and compressing them using RAR
 
REM Notes
REM 	a) In order to use the rar compression, the path of rar.exe should be included in the PATH environment variable
REM 	b) Make sure that the user that MSSQL server uses to run, has write access to the backup folder (http://stackoverflow.com/questions/3960257/cannot-open-backup-device-operating-system-error-5)
 
REM ----- PARAMETERS -----
 
REM Setup below the backup folder (Can contain spaces) (ATTENTION: Folder must exist)
SET BACKUP_FOLDER=C:\backup folder

REM Setup below the username used to connect to the database
SET DATABASE_USER=sa
 
REM Setup below the password used to connect to the database
SET DATABASE_PASSWORD=1234
 
REM Setup below the ip/name of the database server
SET DATABASE_SERVER=mydbserver
 
REM Setup below the database names (space separated)
SET DATABASE_NAMES=db1 db2 db3
 
REM Setup below the days for which the backup files are valid (after that the backup file will be deleted)
SET BACKUPS_VALID_DAYS=5
 
REM ----- END PARAMETERS ----- 
 
 
REM Get the current date and time
FOR /f "tokens=2-4 delims=/ " %%d in ("%date%") DO SET CURRENT_DATE=%%f%%e%%d
FOR /f "tokens=1-2 delims=: " %%q in ("%time%") DO SET CURRENT_TIME=%%q%%r

REM EnableDelayedExpansion is needed so that the loop variable %%X contains the actual value of the loop array
REM (thanks to http://blog.crankybit.com/why-that-batch-for-loop-isnt-working/)
SETLOCAL EnableDelayedExpansion

FOR %%X IN (%DATABASE_NAMES%) DO (	
	REM Create the filepath as [BACKUP_FOLDER + yyyyMMdd_HHmm.bak]
	SET BACKUP_FILE=%BACKUP_FOLDER%\%%X_%CURRENT_DATE%_%CURRENT_TIME%.bak
	SET COMPRESSED_FILE=%BACKUP_FOLDER%\%%X_%CURRENT_DATE%_%CURRENT_TIME%.rar

	REM Create the backup	
	SQLCMD -S %DATABASE_SERVER% -U %DATABASE_USER% -P %DATABASE_PASSWORD% -d master -Q "BACKUP DATABASE [%%X] TO DISK = N'!BACKUP_FILE!' WITH INIT , NOUNLOAD , NAME = N'%%X backup', NOSKIP , STATS = 10, NOFORMAT"

	REM Compress the backup using best compression
	RAR a "!COMPRESSED_FILE!" "!BACKUP_FILE!" -m5

	REM Delete the uncompressed backup file
	DEL "!BACKUP_FILE!"
)

REM Delete files older than BACKUPS_VALID_DAYS days
FORFILES /p "%BACKUP_FOLDER%" /S /M *.rar /D -%BACKUPS_VALID_DAYS% /C "CMD /C del /q @PATH"

The following script is used to perform batch backup of MSSQL server databases. It also compresses the backups using RAR that should already be installed on the computer. It is easy to use it in Windows Scheduler, in order to arrange daily backups of databases

Backup Multiple MSSQL Databases Batch File

backup

Windows PowerShell