@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
วันจันทร์ที่ 15 กันยายน พ.ศ. 2557
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
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
@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
:: 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
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
สมัครสมาชิก:
บทความ (Atom)
