Thailand Generating Batch Scripts

ยินดีต้อนรับ สู่ Thailand Generating Batch Scripts

Blogger นี้จัดทำขึ้นมาเพื่อแบ่งปัน Batch Scripts ที่ผู้เขียนได้จัดทำขึ้นเพื่อใช้ในงานต่างๆ ทั้งการจัดการให้ระบบ Computer สามารถทำงานได้ด้วยความถูกต้อง

พร้อมทั้งเพิ่มความสะดวก สำหรับการจัดการดูแลรวมถึงการประยุกต์ใช้งานต่างๆได้ หวังเพียงให้โลกของเรามีการแบ่งปันเอื้อเฟื้อซึ่งกันและกัน ขอให้ประโยชน์สุขก์จงเกิดแก่มนุษย์โลกทุกท่าน

(เพื่อต่อยอดให้เกิดการแบ่งปันแก่มนุษย์โลกสืบต่อไป)

วันจันทร์ที่ 15 กันยายน พ.ศ. 2557

Count Character in line

@echo off &setLocal EnableDelayedExpansion
set _cha=\
set _input=net use L: "\\abcde\fxtest$\test-dasfadsfiii"
set _str1=%_input: =%
set _str2=%_str1:"=%
call :cntchar %_str2%
echo Number of all Charactor = %n%
echo Number of "!_cha!" = %x%
set _str=_%_str2%
for /l %%a in (1,1,%n%) do (
call set _chx=%%_str:~%%a,1%%
call echo %%a !_chx!
)
endlocal
exit /b

:cntchar
set _strx=%1
set i=-1
set n=0
set x=0
:nextChar
    set /a i+=1
    set c=!_strx:~%i%,1!
    if "!c!"=="" goto endLine
    if "!c!"=="!_cha!" set /a x+=1
    set /a n+=1
    goto nextChar
:endLine
goto:eof

Get Date Time With Leapyear

:gdatetime
if exist "%Temp%.\getdate.vbs" del /q /f "%Temp%.\getdate.vbs"
> "%Temp%.\getdate.vbs" echo Dim dt
>>"%Temp%.\getdate.vbs" echo dt=now
>>"%Temp%.\getdate.vbs" echo 'output format: yyyymmddHHnnss
>>"%Temp%.\getdate.vbs" echo Wscript.echo ((((Year(dt)*100 + Month(dt))*100 + Day(dt))*100 + Hour(dt))*100 + Minute(dt))*100 + Second(dt)
For /f %%g in ('cscript /nologo "%Temp%.\getdate.vbs"') do set _dtm=%%g
set _yyyy=%_dtm:~0,4%
set _mm=%_dtm:~4,2%
set _dd=%_dtm:~6,2%
set _hh=%_dtm:~8,2%
set _nn=%_dtm:~10,2%
set _ss=%_dtm:~12,2%
set /a leapyear=((!(%_yyyy%%%400))^|!(!(%_yyyy%%%100)))^&!(%_yyyy%%%4)
set _mx=31
for %%i in (04,06,09,11) do if %_mm%==%%i set _mx=30
if %_mm%==02 set _mx=28
if %_mm%==02 if %leapyear%==1 set _mx=29
del /q /f "%Temp%.\getdate.vbs"
goto:eof

วันอาทิตย์ที่ 14 กันยายน พ.ศ. 2557

MAPDrives SCripts With Show Drive Letters First and Rename Drive Label with ShareName

:: START SCRIPTS ::
@echo off&setLocal EnableDelayedExpansion
:: All Drive L,M,N,O,P,Q,R,S,T,U,V,W,X,Y
:: Set your Drive Need to Map Drive
set _drivemap=n,s,t,u,w,x,y
call :sharepth1
set _maplist=%Temp%\sharepath1.txt
:stmap
:: Remove All Map drives
net use * /delete /y >nul
:: Show Drive Letters First
set _hkcuexp=HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer
call :shwdir1
:: Map Drive and Rename Drive Label
for /f "tokens=1,2 delims=:" %%a in (!_maplist!) do (
set _uncpth=%%b
set _str=!_uncpth: =!
set _regpth=!_uncpth:\=#!
set _labelpth="%_hkcuexp%\MountPoints2\!_regpth!"
call :cntchar !_str!
for %%i in (!_drivemap!) do (
if /i %%a==%%i net use %%a: "!_uncpth!">nul
if /i %%a==%%i call set _sname=%%_str:~!_lch!%%
if /i %%a==%%i reg add !_labelpth! /v _LabelFromReg /t REG_SZ /d "!_sname!" /f>nul
)
)
del /q /f !_maplist!
exit /b

:shwdir1
set _dchk=0
for /f "Tokens=*" %%i in ('reg query %_hkcuexp% ^|FIND /I /C "ShowDriveLettersFirst"') do set _dchk=%%i
if %_dchk%==0 reg add %_hkcuexp% /v ShowDriveLettersFirst /t REG_DWORD /d 00000004>nul
goto:eof

:cntchar
set _strx=%1
set _cha=\
set i=-1
set n=0
:nextchar
    set /a i+=1
    set c=!_strx:~%i%,1!
    if "!c!"=="" goto endline
        set /a n+=1
    if "!c!"=="!_cha!" set _lch=!n!
    goto nextchar
:endline
goto:eof

:: END SCRIPTS ::

:sharepth1
>"%temp%\sharepath1.txt" echo L:\\<SHARE_SERVER1>\<SHARE_PATH1>
>>"%temp%\sharepath1.txt" echo M:\\<SHARE_SERVER1>\<SHARE_PATH2>
>>"%temp%\sharepath1.txt" echo N:\\<SHARE_SERVER1>\<SHARE_PATH3>
>>"%temp%\sharepath1.txt" echo O:\\<SHARE_SERVER1>\<SHARE_PATH4>
>>"%temp%\sharepath1.txt" echo P:\\<SHARE_SERVER1>\<SHARE_PATH5>
>>"%temp%\sharepath1.txt" echo Q:\\<SHARE_SERVER1>\<SHARE_PATH6>
>>"%temp%\sharepath1.txt" echo R:\\<SHARE_SERVER2>\<SHARE_PATH7>
>>"%temp%\sharepath1.txt" echo S:\\<SHARE_SERVER2>\<SHARE_PATH8>
>>"%temp%\sharepath1.txt" echo T:\\<SHARE_SERVER2>\<SHARE_PATH9>
>>"%temp%\sharepath1.txt" echo U:\\<SHARE_SERVER2>\<SHARE_PATH10>
>>"%temp%\sharepath1.txt" echo V:\\<SHARE_SERVER2>\<SHARE_PATH11>
>>"%temp%\sharepath1.txt" echo W:\\<SHARE_SERVER3>\<SHARE_PATH12>
>>"%temp%\sharepath1.txt" echo X:\\<SHARE_SERVER3>\<SHARE_PATH13>
>>"%temp%\sharepath1.txt" echo Y:\\<SHARE_SERVER3>\<SHARE_PATH14>
goto:eof

วันพุธที่ 28 พฤษภาคม พ.ศ. 2557

Replace Text in myfile.txt

@echo off
:: syntax: replacetxt.bat myfile.txt
setlocal EnableDelayedExpansion
set /p a= enter letter to change:
set /p b= letter to change to:
for /f %%c in (myfile.txt) do (
set input=%%c
set "code=!input:%a%=%b%!"
echo !code!>>new_file.txt
) goto:eof

MSSQL 2005-2008 Scheduled SQLAGENT BACKUPDB FUll + LOG

@echo off
mode 50,20
call :gdatetime
echo ::=========================================::
echo ### -       %_dd%-%_mm%-%_yyyy% %_hh%:%_nn%          - ###
:: SETUP PARAMETER
:: RDRIVE = ROOT DRIRECTORY FOR BACKUP SQLJOB
:: DEFAULT IS SET RDRIVE = D

set RDRIVE=D

:: OLDDAYS = DELETE OLD BACKUP FILE DAYS
:: DEFAULT VALUE OLDDAYS=2 KEEP BACKUP = 3 DAYS

set OLDDAYS=2

::-This Scripts will be add Scheduled for MSSQL 2005-2008
::-1.Compu Backup Database Full Everyday 22:00
::-2.Compu Backup Log Every 3 hrs. Between 9.00-19.00
::-3.Compu Database Update Statistics 6:00
::-This Script need Privilege of System Administrator[sa]
::Create text file dblists.txt in same folder of Batch file
:: type your User into dblists.txt recommended '_' example hc_test
:: Run Batch and sql scripts result file is .\sqljob\mssqljob.sql
echo ::=========================================::
setLocal EnableDelayedExpansion
:dblists
set cntline=0
for /f "usebackq delims=" %%a in (dblists.txt) do (
set /a cntline+=1
)
set N=0
for /f %%z in (dblists.txt) do (
set /a N+=1
set A!N!=%%z
set "_UCASE=ABCDEFGHIJKLMNOPQRSTUVWXYZ"
set "_LCASE=abcdefghijklmnopqrstuvwxyz"
for /l %%a in (0,1,25) do (
   call set "_UC=%%_UCASE:~%%a,1%%
   call set "_LC=%%_LCASE:~%%a,1%%
   call set "A!N!=%%A!N!:!_UC!=!_LC!%%
)
)
if not exist .\sqljob mkdir .\sqljob
if not exist %RDRIVE%:\SQLBackup\Full mkdir %RDRIVE%:\SQLBackup\Full
if not exist %RDRIVE%:\SQLBackup\BK_LOG mkdir %RDRIVE%:\SQLBackup\BK_LOG
set PTSQL=".\sqljob\mssqljob.sql"
:GENSQL
>!PTSQL! echo USE [msdb]
>>!PTSQL! echo GO
>>!PTSQL! echo /****** Object: Job [Compu Full Backup Database Everyday @22:00]   ******/
>>!PTSQL! echo BEGIN TRANSACTION
>>!PTSQL! echo DECLARE @ReturnCode INT
>>!PTSQL! echo SELECT @ReturnCode = 0
>>!PTSQL! echo /****** Object: JobCategory [Database Maintenance]   ******/
>>!PTSQL! echo IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
>>!PTSQL! echo BEGIN
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo.
>>!PTSQL! echo END
>>!PTSQL! echo.
>>!PTSQL! echo DECLARE @jobId BINARY(16)
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Compu Full Backup Database Everyday @22:00',
>>!PTSQL! echo                @enabled=1,
>>!PTSQL! echo                @notify_level_eventlog=0,
>>!PTSQL! echo                @notify_level_email=0,
>>!PTSQL! echo                @notify_level_netsend=0,
>>!PTSQL! echo                @notify_level_page=0,
>>!PTSQL! echo                @delete_level=0,
>>!PTSQL! echo                @description=N'Compupower.co.ltd Maintenance job - Full Backup Database',
>>!PTSQL! echo                @category_name=N'Database Maintenance',
>>!PTSQL! echo                @owner_login_name=N'sa', @job_id = @jobId OUTPUT
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo /****** Object: Step [1 Everyday@22.00]    ******/
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1 Everyday@22.00',
>>!PTSQL! echo                @step_id=1,
>>!PTSQL! echo                @cmdexec_success_code=0,
>>!PTSQL! echo                @on_success_action=1,
>>!PTSQL! echo                @on_success_step_id=0,
>>!PTSQL! echo                @on_fail_action=2,
>>!PTSQL! echo                @on_fail_step_id=0,
>>!PTSQL! echo                @retry_attempts=0,
>>!PTSQL! echo                @retry_interval=0,
>>!PTSQL! echo                @os_run_priority=0, @subsystem=N'TSQL',
>>!PTSQL! echo                @command=N'declare @DaysAgo VARCHAR(50)
>>!PTSQL! echo select @DaysAgo = CAST(DATEADD(d, -%OLDDAYS%, GETDATE()) AS VARCHAR)
>>!PTSQL! echo execute dbo.xp_delete_file 0,N''%RDRIVE%:\SQLBackup\Full\'',N''bak'',@DaysAgo
>>!PTSQL! echo.
>>!PTSQL! echo GO
:: /************************************** Full Backup    ***************************************/ ::
for /l %%n in (1,1,!cntline!) do (
set B1= declare @Path varchar(500^),
set B2=                @DBName varchar(128^)
set B3= select @DBName = ''!A%%n!''
set B4= select @Path = ''%RDRIVE%:\SQLBackup\Full\''
set B5= declare        @FileName varchar(4000^)
set B6=     select @FileName = @Path + convert(varchar(8^),getdate(^),112^) + ''_''
set B7=             + replace(convert(varchar(8^),getdate(^),108^),'':'',''''^) + ''_''
set B8=             + @DBName + ''_Full''
set B9=             + ''.bak''
set B10=         backup database @DBName
set B11= GO
for /l %%n in (1,1,11) do (
echo !B%%n! >>!PTSQL!)
)
:: /************************************** Full Backup    ***************************************/ ::
>>!PTSQL! echo.
>>!PTSQL! echo declare @Path varchar(500) ,
>>!PTSQL! echo                @DBName varchar(128)
>>!PTSQL! echo.
>>!PTSQL! echo select @DBName = ''master''
>>!PTSQL! echo select @Path = ''%RDRIVE%:\SQLBackup\Full\''
>>!PTSQL! echo.
>>!PTSQL! echo declare        @FileName varchar(4000)
>>!PTSQL! echo     select @FileName = @Path + convert(varchar(8),getdate(),112) + ''_''
>>!PTSQL! echo             + replace(convert(varchar(8),getdate(),108),'':'','''') + ''_''
>>!PTSQL! echo             + @DBName + ''_Full''
>>!PTSQL! echo             + ''.bak''
>>!PTSQL! echo.
>>!PTSQL! echo        backup database @DBName
>>!PTSQL! echo GO
>>!PTSQL! echo.
>>!PTSQL! echo declare @Path varchar(500) ,
>>!PTSQL! echo                @DBName varchar(128)
>>!PTSQL! echo.
>>!PTSQL! echo select @DBName = ''msdb''
>>!PTSQL! echo select @Path = ''%RDRIVE%:\SQLBackup\Full\''
>>!PTSQL! echo.
>>!PTSQL! echo declare        @FileName varchar(4000)
>>!PTSQL! echo     select @FileName = @Path + convert(varchar(8),getdate(),112) + ''_''
>>!PTSQL! echo             + replace(convert(varchar(8),getdate(),108),'':'','''') + ''_''
>>!PTSQL! echo             + @DBName + ''_Full''
>>!PTSQL! echo             + ''.bak''
>>!PTSQL! echo.
>>!PTSQL! echo        backup database @DBName
>>!PTSQL! echo GO
>>!PTSQL! echo.
>>!PTSQL! echo ',
>>!PTSQL! echo                @database_name=N'master',
>>!PTSQL! echo                @flags=0
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'EveryDay@22.00',
>>!PTSQL! echo                @enabled=1,
>>!PTSQL! echo                @freq_type=8,
>>!PTSQL! echo                @freq_interval=127,
>>!PTSQL! echo                @freq_subday_type=1,
>>!PTSQL! echo                @freq_subday_interval=0,
>>!PTSQL! echo                @freq_relative_interval=0,
>>!PTSQL! echo                @freq_recurrence_factor=1,
>>!PTSQL! echo                @active_start_date=%_yyyy%%_mm%%_dd%,
>>!PTSQL! echo                @active_end_date=99991231,
>>!PTSQL! echo                @active_start_time=220000,
>>!PTSQL! echo                @active_end_time=235959
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo COMMIT TRANSACTION
>>!PTSQL! echo GOTO EndSave
>>!PTSQL! echo QuitWithRollback:
>>!PTSQL! echo      IF (@@TRANCOUNT ^> 0) ROLLBACK TRANSACTION
>>!PTSQL! echo EndSave:
>>!PTSQL! echo.
>>!PTSQL! echo GO
>>!PTSQL! echo /****** Object: Job [Compu LOG Backup Every 3 hrs. Between 9.00-19.00]    ******/
>>!PTSQL! echo BEGIN TRANSACTION
>>!PTSQL! echo DECLARE @ReturnCode INT
>>!PTSQL! echo SELECT @ReturnCode = 0
>>!PTSQL! echo /****** Object: JobCategory [Database Maintenance]     ******/
>>!PTSQL! echo IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
>>!PTSQL! echo BEGIN
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo.
>>!PTSQL! echo END
>>!PTSQL! echo.
>>!PTSQL! echo DECLARE @jobId BINARY(16)
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Compu LOG Backup Every 3 hrs. Between 9.00-19.00',
>>!PTSQL! echo                @enabled=1,
>>!PTSQL! echo                @notify_level_eventlog=0,
>>!PTSQL! echo                @notify_level_email=0,
>>!PTSQL! echo                @notify_level_netsend=0,
>>!PTSQL! echo                @notify_level_page=0,
>>!PTSQL! echo                @delete_level=0,
>>!PTSQL! echo                @description=N'Compupower.co.ltd Database Maintenance
>>!PTSQL! echo - Compu LOG Backup Every 3 hrs. Between 9.00-19.00',
>>!PTSQL! echo                @category_name=N'Database Maintenance',
>>!PTSQL! echo                @owner_login_name=N'sa', @job_id = @jobId OUTPUT
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo /****** Object: Step [1 Every 3 hrs.]     ******/
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1 Every 3 hrs.',
>>!PTSQL! echo                @step_id=1,
>>!PTSQL! echo                @cmdexec_success_code=0,
>>!PTSQL! echo                @on_success_action=1,
>>!PTSQL! echo                @on_success_step_id=0,
>>!PTSQL! echo                @on_fail_action=2,
>>!PTSQL! echo                @on_fail_step_id=0,
>>!PTSQL! echo                @retry_attempts=0,
>>!PTSQL! echo                @retry_interval=0,
>>!PTSQL! echo                @os_run_priority=0, @subsystem=N'TSQL',
>>!PTSQL! echo                @command=N'declare @DaysAgo VARCHAR(50)
>>!PTSQL! echo select @DaysAgo = CAST(DATEADD(d, -%OLDDAYS%, GETDATE()) AS VARCHAR)
>>!PTSQL! echo execute dbo.xp_delete_file 0,N''%RDRIVE%:\SQLBackup\BK_LOG\'',N''trn'',@DaysAgo
>>!PTSQL! echo GO
:: /************************************** LOG Backup    ***************************************/ ::
for /l %%n in (1,1,!cntline!) do (
set B1= declare @Path varchar(500^) ,
set B2=                @DBName varchar(128^)
set B3= select @DBName = ''!A%%n!''
set B4= select @Path = ''%RDRIVE%:\SQLBackup\BK_LOG\''
set B5= declare        @FileName varchar(4000^)
set B6=     select @FileName = @Path + convert(varchar(8^),getdate(^),112^) + ''_''
set B7=             + replace(convert(varchar(8^),getdate(^),108^),'':'',''''^) + ''_''
set B8=             + @DBName + ''_LOG''
set B9=             + ''.trn''
set B10=        backup log @DBName
set B11= GO
for /l %%n in (1,1,11) do (
echo !B%%n! >>!PTSQL!)
)
:: /************************************** LOG Backup    ***************************************/ ::
>>!PTSQL! echo.
>>!PTSQL! echo ',
>>!PTSQL! echo                @database_name=N'master',
>>!PTSQL! echo                @flags=0
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every 3 hrs.',
>>!PTSQL! echo                @enabled=1,
>>!PTSQL! echo                @freq_type=8,
>>!PTSQL! echo                @freq_interval=127,
>>!PTSQL! echo                @freq_subday_type=8,
>>!PTSQL! echo                @freq_subday_interval=3,
>>!PTSQL! echo                @freq_relative_interval=0,
>>!PTSQL! echo                @freq_recurrence_factor=1,
>>!PTSQL! echo                @active_start_date=%_yyyy%%_mm%%_dd%,
>>!PTSQL! echo                @active_end_date=99991231,
>>!PTSQL! echo                @active_start_time=90000,
>>!PTSQL! echo                @active_end_time=190000
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo COMMIT TRANSACTION
>>!PTSQL! echo GOTO EndSave
>>!PTSQL! echo QuitWithRollback:
>>!PTSQL! echo      IF (@@TRANCOUNT ^> 0) ROLLBACK TRANSACTION
>>!PTSQL! echo EndSave:
>>!PTSQL! echo.
>>!PTSQL! echo GO
>>!PTSQL! echo /****** Object: Job [Compu Database Update Statistics 6:00 AM]     ******/
>>!PTSQL! echo BEGIN TRANSACTION
>>!PTSQL! echo DECLARE @ReturnCode INT
>>!PTSQL! echo SELECT @ReturnCode = 0
>>!PTSQL! echo /****** Object: JobCategory [Database Maintenance]    ******/
>>!PTSQL! echo IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
>>!PTSQL! echo BEGIN
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo.
>>!PTSQL! echo END
>>!PTSQL! echo.
>>!PTSQL! echo DECLARE @jobId BINARY(16)
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Compu Database Update Statistics 6:00 AM',
>>!PTSQL! echo                @enabled=1,
>>!PTSQL! echo                @notify_level_eventlog=0,
>>!PTSQL! echo                @notify_level_email=0,
>>!PTSQL! echo                @notify_level_netsend=0,
>>!PTSQL! echo                @notify_level_page=0,
>>!PTSQL! echo                @delete_level=0,
>>!PTSQL! echo                @description=N'Compupower.co.,ltd Databases Maintenance job
>>!PTSQL! echo - Update Statistics',
>>!PTSQL! echo                @category_name=N'Database Maintenance',
>>!PTSQL! echo                @owner_login_name=N'sa', @job_id = @jobId OUTPUT
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo /****** Object: Step [1]     ******/
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1',
>>!PTSQL! echo                @step_id=1,
>>!PTSQL! echo                @cmdexec_success_code=0,
>>!PTSQL! echo                @on_success_action=1,
>>!PTSQL! echo                @on_success_step_id=0,
>>!PTSQL! echo                @on_fail_action=2,
>>!PTSQL! echo                @on_fail_step_id=0,
>>!PTSQL! echo                @retry_attempts=0,
>>!PTSQL! echo                @retry_interval=0,
>>!PTSQL! echo                @os_run_priority=0, @subsystem=N'TSQL',
>>!PTSQL! echo                @command=N'
:: /************************************** UPDATESTATS    ***************************************/ ::
for /l %%n in (1,1,!cntline!) do (
>>!PTSQL! echo.
>>!PTSQL! echo use !A%%n!
>>!PTSQL! echo go
>>!PTSQL! echo exec sp_updatestats
>>!PTSQL! echo go
>>!PTSQL! echo.
)
:: /************************************** UPDATESTATS    ***************************************/ ::
>>!PTSQL! echo ',
>>!PTSQL! echo                @database_name=N'master',
>>!PTSQL! echo                @flags=0
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every Day',
>>!PTSQL! echo                @enabled=1,
>>!PTSQL! echo                @freq_type=8,
>>!PTSQL! echo                @freq_interval=127,
>>!PTSQL! echo                @freq_subday_type=1,
>>!PTSQL! echo                @freq_subday_interval=0,
>>!PTSQL! echo                @freq_relative_interval=0,
>>!PTSQL! echo                @freq_recurrence_factor=1,
>>!PTSQL! echo                @active_start_date=%_yyyy%%_mm%%_dd%,
>>!PTSQL! echo                @active_end_date=99991231,
>>!PTSQL! echo                @active_start_time=60000,
>>!PTSQL! echo                @active_end_time=235959
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
>>!PTSQL! echo IF (@@ERROR ^<^> 0 OR @ReturnCode ^<^> 0) GOTO QuitWithRollback
>>!PTSQL! echo COMMIT TRANSACTION
>>!PTSQL! echo GOTO EndSave
>>!PTSQL! echo QuitWithRollback:
>>!PTSQL! echo      IF (@@TRANCOUNT ^> 0) ROLLBACK TRANSACTION
>>!PTSQL! echo EndSave:
Endlocal

pause
exit /b

:gdatetime
if exist "%Temp%.\getdate.vbs" del /q /f "%Temp%.\getdate.vbs"
> "%Temp%.\getdate.vbs" ECHO Dim dt
>>"%Temp%.\getdate.vbs" ECHO dt=now
>>"%Temp%.\getdate.vbs" ECHO 'output format: yyyymmddHHnn
>>"%Temp%.\getdate.vbs" ECHO wscript.echo ((year(dt)*100 + month(dt))*100 + day(dt))*10000 + hour(dt)*100 + minute(dt)
For /f %%G in ('cscript /nologo "%Temp%.\getdate.vbs"') do set _dtm=%%G
Set _yyyy=%_dtm:~0,4%
Set _mm=%_dtm:~4,2%
Set _dd=%_dtm:~6,2%
Set _hh=%_dtm:~8,2%
Set _nn=%_dtm:~10,2%
goto:eof

วันพุธที่ 18 มกราคม พ.ศ. 2555

CURRENT DATE TIME AND IDENTIFY LAST 5 MINUTE

@ECHO OFF
:: FIND CURRENT TIME AND IDENTIFY LAST 5 MINUTE
SETlocal enabledelayedexpansion
REM FOR /F "tokens=1,2,3* delims==" %%A IN ('reg query "HKCU\Control Panel\International" ^| find /i "sShortDate"') DO (SET FDATE=%%C)
FOR /F "tokens=1,2,3* delims==: " %%A IN ('DATE /T') DO (SET CDATE=%%B)
FOR /F "tokens=1,2,3* delims==: " %%A IN ('TIME /T') DO (
SET TH=%%A
SET TM=%%B
SET AMPM=%%C
)
ECHO Current Date Time=%CDATE% %TH%:%TM% %AMPM%
SET Z=0
SET X=1
SET THZ=%TH%
:LOOP
IF !X! EQU 6 GOTO :ENDLOOP
SET TH%X%=%THZ%
SET N=%TM%
SET /a N-=%X%
IF !N! LSS 0 GOTO :LST60
IF !N! GEQ 10 SET LS%X%M=%N%
IF !N! LSS 10 SET LS%X%M=0%N%
SET /a X+=1
GOTO :LOOP
:LST60
SET /a N+=60
IF !N! GEQ 10 SET LS%X%M=%N%
IF !N! LSS 10 SET LS%X%M=0%N%
IF !Z! EQU 0 SET /a THZ-=1
SET TH%X%=%THZ%
SET /a X+=1
IF !X! EQU 6 GOTO :ENDLOOP
IF !Z! EQU 1 GOTO :LOOP
SET /a Z+=1
GOTO :LOOP
:ENDLOOP

SET LS1=%CDATE% %TH1%:%LS1M% %AMPM%
SET LS2=%CDATE% %TH2%:%LS2M% %AMPM%
SET LS3=%CDATE% %TH3%:%LS3M% %AMPM%
SET LS4=%CDATE% %TH4%:%LS4M% %AMPM%
SET LS5=%CDATE% %TH5%:%LS5M% %AMPM%
@ECHO Last 1 Mins=%LS1%
@ECHO Last 2 Mins=%LS2%
@ECHO Last 3 Mins=%LS3%
@ECHO Last 4 Mins=%LS4%
@ECHO Last 5 Mins=%LS5%
ENDLOCAL
PAUSE
EXIT

วันอาทิตย์ที่ 8 มกราคม พ.ศ. 2555

Script ping Host name โดย สร้างไฟล์ SVRLIST.TXT ที่มี Host name หรือ IP Address ไว้ข้างในบรรทัดละ 1 Host name ไว้ที่เดียวกันกับ Batch file

:START
PUSHD "%~dp0"
@ECHO OFF
CLS
COLOR 0A
SETLOCAL ENABLEDELAYEDEXPANSION
FOR /F "TOKENS=1,2,* DELIMS= " %%X IN ('DATE /T') DO SET DDD=%%X %%Y
ECHO #########################################################
ECHO ### ###
ECHO HELLO MAN TODAY IS %DDD%
ECHO ### ###
ECHO #########################################################
SET N1=0
SET N2=0
ECHO ---------------------------
FOR /F "TOKENS=*" %%A IN (SVRLIST.TXT) DO (
FOR /F "TOKENS=*" %%I IN ('ping %%A -n 2^|FIND /C /I "reply"') DO (
IF %%I==2 ECHO %%A IS AVAILABLE
IF %%I EQU 1 COLOR 0E
IF %%I EQU 1 SET /A N1=!N1!+1
IF %%I==1 ECHO --### %%A [IS UNSTABLE] ###
IF %%I EQU 0 COLOR 0C
IF %%I EQU 0 SET /A N2=!N2!+1
IF %%I==0 ECHO --### %%A [IS NOT AVAILABLE] ###
ECHO ---------------------------
)
)
IF !N1! NEQ 0 GOTO NOTWORK
IF !N2! NEQ 0 GOTO NOTWORK
GOTO ALLWORK
:NOTWORK
ECHO #########################################################
ECHO ### PLEASE RECHECK YOUR NETWORK CONNECTION ###
ECHO ### THE NETWORK CONNECTION IS UNSTABLE ###
ECHO ### OR ###
ECHO ### SOME SERVER CONNECTION IS UNAVAILABLE ###
ECHO ### NUMBER OF UNSTABLE SERVER = [ %N1% ] ###
ECHO ### NUMBER OF UNAVAILABLE SERVER = [ %N2% ] ###
ECHO #########################################################
GOTO END
:ALLWORK
ENDLOCAL
ECHO #########################################################
ECHO ### ###
ECHO ### !!!! ENJOY YOUR WORK !!!! ###
ECHO ### ###
ECHO #########################################################
:END
POPD
PAUSE

Script Change Gateway and DNS (Winxp,Win7,)

@ECHO OFF
setLocal EnableDelayedExpansion
SET vargw=192.168.0.51
SET xdns1=192.168.0.13
SET xdns2=192.168.0.1
FOR /F "Tokens=1,2,3,4,* delims= " %%i in ('ver') DO set VCHK=%%l
IF %VCHK%==6 SET IPVxS=ipv4
SET IPVxS=ip
FOR /F "Tokens=2 delims==" %%z in ('WMIC NIC where "NetConnectionStatus='2' and NetConnectionID like 'Local Area Connection%%'" GET NetConnectionID /VALUE') DO SET LCON1=%%z
FOR /F "Tokens=1,2,3,4 delims==: " %%a in ('netsh interface %IPVxS% show addresses "%LCON1%" ^| Find /i "IP Address:"') do set MYIP=%%c
netsh interface %IPVxS% set address name = "%LCON1%" source = static addr = %MYIP% mask = 255.255.255.0 gateway = %vargw% gwmetric = 1 1>&2>nul
netsh interface %IPVxS% set dns "%LCON1%" static %xdns1% primary 1>&2>nul
netsh interface %IPVxS% add dns "%LCON1%" %xdns2% 1>&2>nul
Endlocal
pause
exit