Batch File (.bat)

We can use a .bat file to write script that can automate database backup from server.You can take one text file and write this script

@ECHO OFF
CLS
REM SET VARIABLES VALUES 

SET SqlServer=Code2night\SQL2017
SET InstanceName=Code2night\SQL2017
SET Username=sa
SET Password=Admin123

SET BaseFolderPath=C:\DatabaseScript\DatabaseScriptBackup
SET TodayDate=%DATE:/=%
SET SubFolder=%Backup%
SET LocalFolder=%BaseFolderPath%\%SubFolder%
SET SourceFile=C:\DatabaseScript\Backup
SET limit = (Get-Date).AddMinutes(-1)
SET Databases=%LocalFolder%\Databases.txt
IF NOT EXIST %LocalFolder% mkdir %LocalFolder%

:: *****************************************************************************

REM date
set mydate=%date:~7,2%%date:~4,2%%date:~10,4%

REM hour
set hour=%time:~0,2%
if "%hour:~0,1%" == " " set hour=0%hour:~1,1%

REM min
set min=%time:~3,2%
if "%min:~0,1%" == " " set min=0%min:~1,1%

REM secs
set secs=%time:~6,2%
if "%secs:~0,1%" == " " set secs=0%secs:~1,1%

ECHO DB Backup finished...

REM SETLOCAL
setlocal EnableDelayedExpansion

REM SAVE DATABASES LIST IN TEMP FILE
SqlCmd -S %SqlServer% -U %Username% -P %Password% -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name]  IN ('ALISTrucking','ALISTruckingClean','ALISTruckingPreProd')" > "%Databases%"

REM SAVE BACKUP OF EACH DATABASE

FOR /F "tokens=* delims= " %%I IN (%Databases%) DO (
 
ECHO Backing up database: %%~nI 

REM SAVE BACKUP OF EACH DATABASE into folder with DDMMYYHHMMSS
sqlcmd -S %SqlServer% -U %Username% -P %Password%  -Q "BACKUP DATABASE %%~nI TO  DISK = '%LocalFolder%\%%~nI_%mydate%%hour%%min%%secs%.bak' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10;"

ECHO.)

REM DELETE TEMP DATABASES LIST
IF EXIST "%Databases%" DEL /F /Q "%Databases%"

ENDLOCAL

ECHO DB Backup finished...

 

SqlServer: This must be the sql server instance name to connect to your sql server.

UserName: This must be the sql server Login usernname to connect to your sql server database.

Password: This must be the sql server login password to connect to your sql server.

BaseFolderPath: This must be the location of folder where you want your database backups to be saved.

Executing Batch Script

AFter adding the script you have to save the file as .bat extension. And then you have to double click on the file to save the script. After that go to the location and check if you have your database backups there.

So, this is how you can create Batch Script for Creating Database backups from Sql server