U ovom poglavlju ćemo pogledati, kako pomoću T-SQL naredbi napraviti DB
Maintainance Plan (DBMP). DBMP je primjereniji od samog snimanja rezervne kopije
(backup), jer pregleda i strukturu podataka, popravi manje podatkovne greške i
oslobodi eventualan prostor u bazi podataka.
Kreiranje DBMP-ja
 |
U ovoj skripti je dovoljno da samo promijenite globalne varijable i pokrenete
skriptu!
|
use master
begin transaction
declare
@sPlanID nchar(36),
@sPlanName varchar(100),
@sLogDir varchar(100),
@sBackupDir varchar(100),
@sJobName varchar(255),
@sParam varchar(1000),
@sDBName varchar(100),
@iStartDate int,
@iBackupTime int,
@iOptiTime int,
@ReturnCode INT ,
@JobID0 nchar(36),
@JobID1 nchar(36),
@JobID2 nchar(36),
@JobID3 nchar(36),
@JobID4 nchar(36),
@JobID5 nchar(36),
@JobID6 nchar(36),
@JobID7 nchar(36),
@JobIDD nchar(36)
-- tu inicializiramo globalne spremenljivke
set @sDBName = 'Blaz' -- ime baze / spremeni po potrebi
set @sPlanName = 'DB Maintenance Blaz' -- ime maintainance plana / spremeni po potrebi
set @sLogDir = 'C:\MSSQL7\LOG' -- pot za log s serverja / spremeni po potrebi
set @sBackupDir = 'C:\MSSQL7\BACKUP' -- pot za backup s serverja / spremeni po potrebi
set @iStartDate = 20020425 -- začetni datum, od katerega naprej se izvaja (25.4.2002)
set @iBackupTime = 20000 -- ura, ko naj začne z backupom (02:00:00)
set @iOptiTime = 10000 -- ura, ko se delajo optimizacije (01:00:00)
-- dobimo njegov ID in ga vstavimo v PLANS tabelo
select @sPlanID = NEWID()
insert msdb.dbo.sysdbmaintplans (plan_id, plan_name, max_history_rows, remote_history_server, max_remote_history_rows)
values (@sPlanID, @sPlanName, 1000, '', 0)
-- sedaj pa začnemo z jobi
--!! select @PlanID = N'1403015E-AAC4-4481-81EF-84BCAF0CBC91'
set @sJobName = 'DB Backup Job for DB Maintenance Plan '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID4 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
set @sParam = 'exec master.dbo.xp_sqlmaint N''-PlanID '+@sPlanID+' -Rpt "'+@sLogDir+'\DBMP (Backup) '+@sDBName+'.txt" -DelTxtRpt 1WEEKS -WriteHistory -VrfyBackup -BkUpOnlyifClean -CkDBRepair -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1 -BkExt ""'''
exec @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID4,
@step_id = 1,
@step_name = N'Step 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID4,
@name = N'Schedule 1',
@enabled = 1,
@freq_type = 4,
@active_start_date = @iStartDate,
@active_start_time = @iBackupTime,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID4,
@server_name = N'(local)'
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
set @sJobName = 'Transaction Log Backup Job for DB Maintenance Plan '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID6 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
set @sParam = 'exec master.dbo.xp_sqlmaint N''-PlanID '+@sPlanID+' -Rpt "'+@sLogDir+'\DBMP (Log Backup) '+@sDBName+'.txt" -DelTxtRpt 1WEEKS -WriteHistory -VrfyBackup -BkUpOnlyifClean -CkDBRepair -BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 1 -BkExt ""'''
exec @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID6,
@step_id = 1,
@step_name = N'Step 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID6,
@name = N'Schedule 1',
@enabled = 1,
@freq_type = 8,
@active_start_date = 0,
@active_start_time = 0,
@freq_interval = 126,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID6,
@server_name = N'(local)'
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
set @sJobName = 'Integrity Checks Job for DB Maintenance Plan '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID2 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
set @sParam = 'exec master.dbo.xp_sqlmaint N''-PlanID '+@sPlanID+' -Rpt "'+@sLogDir+'\DBMP (Integrity) '+@sDBName+'.txt" -DelTxtRpt 1WEEKS -WriteHistory -CkDBRepair '''
exec @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID2,
@step_id = 1,
@step_name = N'Step 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID2,
@name = N'Schedule 1',
@enabled = 1,
@freq_type = 4,
@active_start_date = @iStartDate,
@active_start_time = 0,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID2,
@server_name = N'(local)'
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
set @sJobName = 'Optimizations Job for DB Maintenance Plan '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID0 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
set @sParam = 'exec master.dbo.xp_sqlmaint N''-PlanID '+@sPlanID+' -Rpt "'+@sLogDir+'\DBMP (Optimisations) '+@sDBName+'.txt" -DelTxtRpt 1WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '''
exec @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID0,
@step_id = 1,
@step_name = N'Step 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID0,
@name = N'Schedule 1',
@enabled = 1,
@freq_type = 4,
@active_start_date = @iStartDate,
@active_start_time = @iOptiTime,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID0,
@server_name = N'(local)'
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
if (select Count(*)
from msdb.dbo.sysdbmaintplan_databases
where plan_id = @sPlanID AND database_name = @sDBName) < 1
insert msdb.dbo.sysdbmaintplan_databases (plan_id, database_name) values (@sPlanID, @sDBName)
delete msdb.dbo.sysdbmaintplan_jobs where plan_id = @sPlanID
insert msdb.dbo.sysdbmaintplan_jobs
values (@sPlanID, @JobID0)
insert msdb.dbo.sysdbmaintplan_jobs
values (@sPlanID, @JobID2)
insert msdb.dbo.sysdbmaintplan_jobs
values (@sPlanID, @JobID4)
insert msdb.dbo.sysdbmaintplan_jobs
values (@sPlanID, @JobID6)
commit transaction
goto EndSave
QuitWithRollback: if (@@TRANCOUNT > 0) rollback transaction
EndSave:
Pregled DBMP-ova
Ako želimo pregledati, koji DBMP-ovi su aktivni, to možemo napraviti naredbom
select *
from msdb.dbo.sysdbmaintplans