SQLBKUP

This little gem of a Batch program will backup ALL of your SQL databases. Just point it to the SQL Data Base in the MYDB variable.

Written for nightly use on WinServer 2003. I wrote this program because believe it or not, the only alternative was to purchase a $20k program with annual maintenance agreement.

Could have written program in REXX but liked idea of running it on any server without the REXX interpreter.

So here is my little DOS Batch program to do the same as a $20k backup program. It ran flawlessly for several clients over many years.

   @ECHO OFF
   SETLOCAL enableextensions
   REM --- Check If WMIC Service is Available ---
   WMIC.EXE Alias /? >NUL 2>&1 || GOTO S_EXIT
   REM --- Use WMIC to Retrieve Date and Time ---
     FOR /F "skip=1 tokens=1-6" %%G IN ('WMIC Path Win32_LocalTime Get Day^,Hour^,Minute^,Month^,Second^,Year /Format:table') DO (
       IF "%%~L"=="" GOTO SDONE
       Set _yyyy=%%L
       Set _mm=00%%J
       Set _dd=00%%G
       Set _hour=00%%H
       Set _minute=00%%I
     )
   :SDONE
   REM --- Pad Numbers with Leading Zeros ---
     Set _mm=%_mm:~-2%
     Set _dd=%_dd:~-2%
     Set _hour=%_hour:~-2%
     Set _minute=%_minute:~-2%
   REM --- Set into Date Var ---
     Set NowDate=%_yyyy%%_mm%%_dd%_%_hour%%_minute%
   REM --- Define VARS ---
     Set MYDB="TOW-BILLINGSRV\TOWBILLINGSQL"
     Set MPATH="C:\SQLBackup\%NowDate%"
     MKDIR %MPATH%
   REM --- Build List of Databases to Backup ---
     SET DBList=%SystemDrive%SQLDBList.txt
     SqlCmd -E -S %MYDB% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('master','model','msdb','tempdb')" > "%DBList%"
   REM --- Backup Database ---
     FOR /F "tokens=*" %%I IN (%DBList%) DO (
       ECHO Backing up database: %%I
       SqlCmd -E -S %MYDB% -Q "BACKUP DATABASE [%%I] TO Disk='C:\SQLBackup\%NowDate%\%%I.bak'"
       ECHO.
     )
   REM --- Delete Temp File
     IF EXIST "%DBList%" DEL /F /Q "%DBList%"
   :S_EXIT
   ENDLOCAL