Advanced Search

Backup Multiple MSSQL Databases Batch File

/ Published in: Windows PowerShell
Save to your folder(s)
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

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. @ECHO OFF
  2. REM Backing up multiple MSSQL Databases and compressing them using RAR
  3.  
  4. REM Notes
  5. REM 	a) In order to use the rar compression, the path of rar.exe should be included in the PATH environment variable
  6. 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)
  7.  
  8. REM ----- PARAMETERS -----
  9.  
  10. REM Setup below the backup folder (Can contain spaces) (ATTENTION: Folder must exist)
  11. SET BACKUP_FOLDER=C:\backup folder
  12.  
  13. REM Setup below the username used to connect to the database
  14. SET DATABASE_USER=sa
  15.  
  16. REM Setup below the password used to connect to the database
  17. SET DATABASE_PASSWORD=1234
  18.  
  19. REM Setup below the ip/name of the database server
  20. SET DATABASE_SERVER=mydbserver
  21.  
  22. REM Setup below the database names (space separated)
  23. SET DATABASE_NAMES=db1 db2 db3
  24.  
  25. REM Setup below the days for which the backup files are valid (after that the backup file will be deleted)
  26. SET BACKUPS_VALID_DAYS=5
  27.  
  28. REM ----- END PARAMETERS ----- 
  29.  
  30.  
  31. REM Get the current date and time
  32. FOR /f "tokens=2-4 delims=/ " %%d in ("%date%") DO SET CURRENT_DATE=%%f%%e%%d
  33. FOR /f "tokens=1-2 delims=: " %%q in ("%time%") DO SET CURRENT_TIME=%%q%%r
  34.  
  35. REM EnableDelayedExpansion is needed so that the loop variable %%X contains the actual value of the loop array
  36. REM (thanks to http://blog.crankybit.com/why-that-batch-for-loop-isnt-working/)
  37. SETLOCAL EnableDelayedExpansion
  38.  
  39. FOR %%X IN (%DATABASE_NAMES%) DO (	
  40. 	REM Create the filepath as [BACKUP_FOLDER + yyyyMMdd_HHmm.bak]
  41. 	SET BACKUP_FILE=%BACKUP_FOLDER%\%%X_%CURRENT_DATE%_%CURRENT_TIME%.bak
  42. 	SET COMPRESSED_FILE=%BACKUP_FOLDER%\%%X_%CURRENT_DATE%_%CURRENT_TIME%.rar
  43.  
  44. 	REM Create the backup	
  45. 	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"
  46.  
  47. 	REM Compress the backup using best compression
  48. 	RAR a "!COMPRESSED_FILE!" "!BACKUP_FILE!" -m5
  49.  
  50. 	REM Delete the uncompressed backup file
  51. 	DEL "!BACKUP_FILE!"
  52. )
  53.  
  54. REM Delete files older than BACKUPS_VALID_DAYS days
  55. FORFILES /p "%BACKUP_FOLDER%" /S /M *.rar /D -%BACKUPS_VALID_DAYS% /C "CMD /C del /q @PATH"

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.