@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
ไม่มีความคิดเห็น:
แสดงความคิดเห็น