SQL2000关于用代码进行备份等操作.docx

SQL2000关于用代码进行备份等操作-参考/*完整备份作业*/-完整备份,每周一次USE MasterGOdeclare str varchar(100)set str=D:DBtextjgjDBABakFullBak+replace(replace(replace(convert(varchar,getdate(),20),-,), ,),:,)+.bakBACKUP DATABASE demo TO DISK=str WITH RETAINDAYS=15,NOFORMAT,NOINIT, NAME=NDemo完整备份,SKIP,NOREWIND, NOUNLOAD,STATS=10GO/*差异备份作业*/-截断日志USE MasterGOBACKUP LOG Demo WITH NO_LOGGO -收缩日志文件USE DemoGODBCC SHRINKFILE (NDemo_log,0,TRUNCATEONLY)GO -差异备份,每天一次USE MasterGOdeclare str varchar(100)set str=D:DBtextjgjDBABakDiffBak+replace(replace(replace(convert(varchar,getdate(),20),-,), ,),:,)+.diffBACKUP DATABASE Demo TO DISK=str WITH DIFFERENTIAL,RETAINDAYS=8,NOFORMAT,NOINIT, NAME=NDemo差异备份,SKIP,NOREWIND, NOUNLOAD,STATS=10GO/*日志备份作业*/-日志备份,每小时一次USE DemoGOdeclare str varchar(100)set str=D:DBtextjgjDBABaklogbak+replace(replace(replace(convert(varchar,getdate(),20),-,), ,),:,)+.trnBACKUP LOG Demo TO DISK=str WITH RETAINDAYS=3,NOFORMAT,NOINIT, NAME=NDemo日志备份,SKIP,NOREWIND, NOUNLOAD,STATS=10GO-删除过期的备份文件,每天两次declare str varchar(100),dir varchar(100),fileName varchar(30)set dir=del D:DBtextjgjDBABakset filename=left(replace(replace(replace(convert(varchar,getdate()-15,20),-,), ,),:,),8)set str=dir+fullbak+filename+*.bakexec xp_cmdshell strset filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),-,), ,),:,),8)set str=dir+diffbak+filename+*.diffexec xp_cmdshell strset filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),-,), ,),:,),8)set str=dir+logbak+filename+*.trnexec xp_cmdshell str=SQL企业管理器提取的SQL脚本- 2012-02-22/10:10 上生成的脚本- 由: HEQUANXINTyler- 服务器: (LOCAL)BEGIN TRANSACTION DECLARE JobID BINARY(16) DECLARE ReturnCode INT SELECT ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = NDatabase Maintenance) 1EXECUTE msdb.dbo.sp_add_category name = NDatabase Maintenance- 删除同名的警报（如果有的话）。SELECT JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = NDB 维护计划“FGPSN备份计划”的DB 备份作业。) IF (JobID IS NOT NULL) BEGIN - 检查此作业是否为多重服务器作业 IF (EXISTS (SELECT *FROM msdb.dbo.sysjobserversWHERE (job_id = JobID) AND (server_id 0)BEGIN- 已经存在，因而终止脚本RAISERROR (N无法导入作业“DB 维护计划“FGPSN备份计划”的DB 备份作业。”，因为已经有相同名称的多重服务器作业。, 16, 1)GOTO QuitWithRollback ENDELSE- 删除本地作业EXECUTE msdb.dbo.sp_delete_job job_name = NDB 维护计划“FGPSN备份计划”的DB 备份作业。SELECT JobID = NULLENDBEGIN- 添加作业EXECUTE ReturnCode = msdb.dbo.sp_add_job job_id = JobID OUTPUT , job_name = NDB 维护计划“FGPSN备份计划”的DB 备份作业。, owner_login_name = Nsa, description = N没有可用的描述。, category_name = NDatabase Maintenance, enabled = 1, notify_level_email = 0, notify_level_page = 0, notify_level_netsend = 0, notify_level_eventlog = 2, delete_level= 0IF (ERROR 0 OR ReturnCode 0) GOTO QuitWithRollback- 添加作业步骤EXECUTE ReturnCode = msdb.dbo.sp_add_jobstep job_id = JobID, step_id = 1, step_name = N第1 步, command = NEXECUTE master.dbo.xp_sqlmaint N-PlanID FFC3A233-504F-4735-BD9A-7E96C9AD551D -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1MONTHS -BkExt BAK, database_name = Nmaster, server = N, database_user_name = N, subsystem = NTSQL, cmdexec_success_code = 0, flags = 4, retry_attempts = 0, retry_interval = 0, output_file_name = N, on_success_step_id = 0, on_success_action = 1, on_fail_step_id = 0, on_fail_action = 2IF (ERROR 0 OR ReturnCode 0) GOTO QuitWithRollbackEXECUTE ReturnCode = msdb.dbo.sp_update_job job_id = JobID, start_step_id = 1IF (ERROR 0 OR ReturnCode 0) GOTO QuitWithRollback- 添加作业调度EXECUTE ReturnCode = msdb.dbo.sp_add_jobschedule job_id = JobID, name = N第1 调度, enabled = 1, freq_type = 4, active_start_date = 20120222, active_start_time = 20000, freq_interval = 1, freq_subday_type = 1, freq_subday_interval = 0, freq_relative_interval = 0, freq_recurrence_factor = 0, active_end_date = 99991231, active_end_time = 235959IF (ERROR 0 OR ReturnCode 0) GOTO QuitWithRollback- 添加目标服务器EXECUTE ReturnCode = msdb.dbo.sp_add_jobserver job_id = JobID, server_name = N(local)IF (ERROR 0 OR ReturnCode 0) GOTO QuitWithRollbackENDCOMMIT TRANSACTION GOTO EndSave QuitWithRollback:IF (TRANCOUNT 0) ROLLBACK TRANSACTIONEndSave:实战应用declarefilenamenvarchar(100)setfilename=F:backupmis002_gzglmis002_gzgl_db_+replace(replace(replace(convert(varchar,getdate(),120),-,),),:,)+.BAKBACKUPDATABASEmis002_gzglTODISK=filenameWITHNOINIT,NOUNLOAD,NAME=Nmis002_gzgl备份,NOSKIP,STATS=10,NOFORMATdeclare str varchar(100),dir varchar(100),fileName varchar(30)set dir=del F:backupmis002_gzglmis002_gzgl_db_set filename=convert(CHAR(8),GETDATE() -10 ,112)set str=dir+fil