@ECHO OFF

set year=%DATE:~10,4%
set day=%DATE:~7,2%
set mnt=%DATE:~4,2%
set hr=%TIME:~0,2%
set min=%TIME:~3,2%

IF %day% LSS 10 SET day=0%day:~1,1%
IF %mnt% LSS 10 SET mnt=0%mnt:~1,1%
IF %hr% LSS 10 SET hr=0%hr:~1,1%
IF %min% LSS 10 SET min=0%min:~1,1%

set backuptime=%year%-%day%-%mnt%-%hr%-%min%

:: Name of the database user with rights to all tables
set dbuser=root

:: Password for the database user
set dbpass=root

:: Error log path - Important in debugging your issues
set errorLogPath="C:\CCTNS-MySQLBackups\backupfiles\dumperrors.txt"

:: MySQL EXE Path
set mysqldumpexe="C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysqldump.exe"

:: MySQL EXE Path
set mysqld="C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysql.exe"

:: Error log path
set backupfldr=C:\CCTNS-MySQLBackups\backupfiles\

:: Path to data folder which may differ from install dir
set datafldr="C:\ProgramData\MySQL\MySQL Server 5.5\data"

:: Path to zip executable
set zipper="C:\CCTNS-MySQLBackups\zip\7z.exe"

:: Number of days to retain .zip backup files 
set retaindays=10

:: DONE WITH SETTINGS


IF EXIST C:\CCTNS-MySQLBackups\zip\lastbackup.txt DEL /F C:\CCTNS-MySQLBackups\zip\lastbackup.txt
IF EXIST C:\CCTNS-MySQLBackups\zip\thumb.txt DEL /F C:\CCTNS-MySQLBackups\zip\thumb.txt

:: GO FORTH AND BACKUP EVERYTHING!

set datafldr="C:\ProgramData\MySQL\MySQL Server 5.5\data"
%mysqld% --user=%dbuser% --password=%dbpass% -Dcctns_offline_db_sync_proc -e"select REPLACE(REPLACE(REPLACE(SubString(concat(PS,'_',NOW()),1,33),'-','_'),':','_'),' ','_') from m_police_station where PS_CD in (select PS_CD from m_registration_seq_num where REG_TYPE_CD=1) and LANG_CD=99 into outfile 'C:\\CCTNS-MySQLBackups\\zip\\lastbackup.txt'";
setlocal EnableDelayedExpansion
for /f "delims=" %%a in (C:\CCTNS-MySQLBackups\zip\lastbackup.txt) do ( 
  set "output=%%a"
)
set filename=%output%

%mysqld% --user=%dbuser% --password=%dbpass% -Dcctns_offline_db_sync_proc -e"SELECT FN_GET_PWD_DATA() into outfile 'C:\\CCTNS-MySQLBackups\\zip\\thumb.txt'";
setlocal EnableDelayedExpansion
for /f "delims=" %%a in (C:\CCTNS-MySQLBackups\zip\thumb.txt) do ( 
  set "pass=%%a"
)
set pwd=%pass%

:: Switch to the data directory to enumerate the folders
pushd %datafldr%
ECHO "Backing up Database Please Wait for few seconds"
echo "Pass each name to mysqldump.exe and output an individual .sql file for each"

@echo off

FOR /D %%F IN (*) DO (

IF [%%F]==[cctns_offline_db_sync_proc] (
SET %%F=!%%F:@002d=-!
%mysqldumpexe% --user=%dbuser% --password=%dbpass% --databases   --routines --log-error=%errorLogPath% %%F > "%backupfldr%%filename%.sql"
) ELSE (
echo Skipping DB backup for performance_schema
)
)

::Adding Required Statment
Echo Update t_check_login_sync_ntl SET FUNC_STATUS='C',LOGIN_STATUS='D'; >> "%backupfldr%%filename%.sql"
:: .zip option clean but not as compressed

%zipper% a -tzip -pntl@%pwd% -r  "%backupfldr%%filename%.7z" "%backupfldr%%filename%.sql"

echo "Deleting all the files ending in .sql only"
 Del C:\CCTNS-MySQLBackups\backupfiles\*.sql

echo "Deleting zip files older than 10 days now"
Forfiles -p %backupfldr% -s -m *.* -d -%retaindays% -c "cmd /c del /q @path"
echo "done"
popd

C:
   IF NOT EXIST D:\$RECYCLE.BIN GOTO NOWINDIR
   CD \ 
    D:
echo D Drive is available so the backup file will be copied to Mysql_Backup folder into the local disk D
GOTO LAST
   :NOWINDIR

   IF NOT EXIST E:\$RECYCLE.BIN GOTO NOWINDIR
   CD \ 
    E:
echo E Drive is available so the backup file will be copied to Mysql_Backup folder into the local disk E
GOTO LAST
   :NOWINDIR

   IF NOT EXIST G:\$RECYCLE.BIN GOTO NOWINDIR
   CD \ 
    G:
echo G Drive is available so the backup file will be copied to Mysql_Backup folder into the local disk F
GOTO LAST
   :NOWINDIR

   IF NOT EXIST Q:\$RECYCLE.BIN GOTO NOWINDIR
   CD \ 
    Q:
echo Q Drive is available so the backup file will be copied to Mysql_Backup folder into the local disk Q
GOTO LAST
   :NOWINDIR

:LAST
Mkdir Mysql_Backup
xcopy C:\CCTNS-MySQLBackups\backupfiles\*.* .\Mysql_Backup\ /Y
Forfiles -p .\Mysql_backup\ -s -m *.* -d -%retaindays% -c "cmd /c del /q @path"
xcopy "C:\ProgramData\MySQL\MySQL Server 5.5\data\ibdata1" .\Mysql_Backup\Schema_File\ /Y /D

 setLocal DisableDelayedExpansion
 pushd  C:\CCTNS-MySQLBackups\backupfiles\
 setLocal EnableDelayedExpansion
 for /f "tokens=* delims= " %%G in ('dir/b/od') do (set newest=%%G)

setlocal
set file="C:\CCTNS-MySQLBackups\backupfiles\%newest%"
set maxbytesize=2545728
echo %file%

FOR /F "usebackq" %%A IN ('%file%') DO set size=%%~zA

if %size% LSS %maxbytesize% (
    ::echo.File is ^< %maxbytesize% bytes
GOTO ErrorMessage
) ELSE (
    ::echo.File is ^>= %maxbytesize% bytes
GOTO END
)
:ErrorMessage
echo x=msgbox("There is a problem in Creating backup please contact to Helpdesk immediately otherwise your data will be lost",16,"Error in Creating Backup") > %temp%\TEMPmessage.vbs
call %temp%\TEMPmessage.vbs
del %temp%\TEMPmessage.vbs /f /q
taskkill /IM cmd.exe
:END
 POPD
IF EXIST C:\CCTNS-MySQLBackups\zip\thumb.txt DEL /F C:\CCTNS-MySQLBackups\zip\thumb.txt
echo "done"
popd