1、USE msdbGO/* 对象: Job FullBackup 脚本日期: 07/04/2012 11:24:21 */BEGIN TRANSACTIONDECLARE ReturnCode INTSELECT ReturnCode = 0/* 对象: JobCategory Database Maintenance 脚本日期: 07/04/2012 11:24:21 */IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=NDatabase Maintenance AND category_class=1)BEG
2、INEXEC ReturnCode = msdb.dbo.sp_add_category class=NJOB, type=NLOCAL, name=NDatabase MaintenanceIF (ERROR 0) GOTO QuitWithRollbackENDDECLARE jobId BINARY(16)EXEC ReturnCode = msdb.dbo.sp_add_job job_name=NFullBackup, enabled=1, notify_level_eventlog=0, notify_level_email=0, notify_level_netsend=0, n
3、otify_level_page=0, delete_level=0, description=N对于全备份，需要在虚拟目录下建立二级子目录“LastWeek“,在全备份的时候，将二级子目录下的备份文件删除，将根目录下的文件移动到二级子目录下，再将数据库机器上的全备份文件移动到虚拟根目录下。这样可以保存两周的备份文件。, category_name=NDatabase Maintenance, owner_login_name=Nsa, job_id = jobId OUTPUTIF (ERROR 0) GOTO QuitWithRollback/* 对象: Step FullBackup 脚
4、本日期: 07/04/2012 11:24:22 */EXEC ReturnCode = msdb.dbo.sp_add_jobstep job_id=jobId, step_name=NFullBackup, step_id=1, cmdexec_success_code=0, on_success_action=1, on_success_step_id=0, on_fail_action=2, on_fail_step_id=0, retry_attempts=0, retry_interval=0, os_run_priority=0, subsystem=NTSQL, command
5、=NDECLARE filePath NVARCHAR(200)DECLARE SourcePath NVARCHAR(200)DECLARE TargetPath NVARCHAR(200)DECLARE VirtualPath NVARCHAR(200)DECLARE MapCmd NVARCHAR(200)DECLARE MoveCmd NVARCHAR(200)DECLARE DeleteFullBackCmd NVARCHAR(200)DECLARE DeleteDiffBackCmd NVARCHAR(200)DECLARE UnmapCmd NVARCHAR(200)SET Ta
6、rgetPath = http:/192.168.70.162/backupSET SourcePath = e:BackupSET VirtualPath = Z:SET MapCmd = net use + VirtualPath + + TargetPath EXEC masterxp_cmdshell MapCmdset filePath = convert(NVARCHAR(4),year(getdate() + - + convert(NVARCHAR(2),datepart(week,getdate()set filePath = SourcePath +DB_FullBak_
7、+ filePath + .bakBACKUP DATABASE YAMATOWMS TO DISK = filePath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMATSET DeleteFullBackCmd = Del + VirtualPath + LastWeek*FullBak*.bakSET DeleteDiffBackCmd = Del + VirtualPath + LastWeek*DiffBak*.bakexec masterxp_cmdshell DeleteFullBackCmd exec masterxp
8、_cmdshell DeleteDiffBackCmd SET MoveCmd = Move + VirtualPath + *Bak*.bak + VirtualPath + LastWeekexec masterxp_cmdshell MoveCmdSET MoveCmd = Move + filePath + + VirtualPathexec masterxp_cmdshell MoveCmd SET UnmapCmd = net use + VirtualPath + /deleteexec masterxp_cmdshell UnmapCmd, database_name=NYAM
9、ATOWMS, flags=0IF (ERROR 0) GOTO QuitWithRollbackEXEC ReturnCode = msdb.dbo.sp_update_job job_id = jobId, start_step_id = 1IF (ERROR 0) GOTO QuitWithRollbackEXEC ReturnCode = msdb.dbo.sp_add_jobschedule job_id=jobId, name=NFullBackupPlan, enabled=1, freq_type=8, freq_interval=1, freq_subday_type=1,
10、freq_subday_interval=0, freq_relative_interval=0, freq_recurrence_factor=1, active_start_date=20100223, active_end_date=99991231, active_start_time=0, active_end_time=235959IF (ERROR 0) GOTO QuitWithRollbackEXEC ReturnCode = msdb.dbo.sp_add_jobserver job_id = jobId, server_name = N(local)IF (ERROR 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:IF (TRANCOUNT 0) ROLLBACK TRANSACTIONEndSave:
