Backup SQL database
sqlcmd -Q "BACKUP DATABASE <mydatabase> TO DISK='C:\Backup\mydatabase.bak'" 

Example of a script which backs up database with today's date and moves to a network folder.

@echo off
set LOGFILE=SQL-ScriptBackup.log
set DBNAME=cvp_dn_config
set BACKUP-FOLDER=D:\X-Backup



FOR /f "tokens=1-4 delims=/ " %%A IN ('date /t') DO (
set nday=%%A
set nmonth=%%B
set nyear=%%C
)
set SORTDATE=%nyear%%nmonth%%nday%

FOR /f "tokens=1-2 delims=: " %%A IN ('time /t') DO (
set hour=%%A
set min=%%B
)

set SORTTIME=%hour%%min%
set TIMESTAMP=%sortdate%-%sorttime%
echo Timestamp: %TIMESTAMP%



if exist "%BACKUP-FOLDER%" (
goto :start
) else (
md %BACKUP-FOLDER%
)

:start

echo ----------------BACKUP--------------------------->> %LOGFILE%
echo Date: %date% >> %LOGFILE%
echo Start of Script Time: %time% >> %LOGFILE%


: SQL COMMAND
echo. >> %LOGFILE%
echo sqlcmd -Q "BACKUP DATABASE %DBNAME% TO DISK='%BACKUP-FOLDER%\%DBNAME%_%SORTDATE%.bak'" >> %LOGFILE%
sqlcmd -Q "BACKUP DATABASE %DBNAME% TO DISK='%BACKUP-FOLDER%\%DBNAME%_%SORTDATE%.bak'" >> %LOGFILE%

echo End of Script Time: %time% >> %LOGFILE%


move %BACKUP-FOLDER%\%DBNAME%_%SORTDATE%.bak \\123.123.123.123\X-Backup\ >> %LOGFILE%
echo ------------------------------------------------->> %LOGFILE%


exit
  • tech-notes/scripts/sql-backup.txt
  • Last modified: 2018/05/17 20:05
  • by gerardorourke