SQLServer_FullBackup脚本.docVIP

USE [msdb] GO /****** 对象: Job [FullBackup] 脚本日期: 07/04/2012 11:24:21 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @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) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=NJOB, @type=NLOCAL, @name=NDatabase Maintenance IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback END DECLARE @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, @notify_level_page=0, @delete_level=0, @description=N对于全备份，需要在虚拟目录下建立二级子目录“LastWeek,在全备份的时候，将二级子目录下的备份文件删除，将根目录下的文件移动到二级子目录下，再将数据库机器上的全备份文件移动到虚拟根目录下。这样可以保存两周的备份文件。, @category_name=NDatabase Maintenance, @owner_login_name=Nsa, @job_id = @jobId OUTPUT IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback /****** 对象: Step [FullBackup] 脚本日期: 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=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 @TargetPath = 62/backup SET @SourcePath = e:\Backup\ SET @VirtualPath = Z: SET @MapCmd = net use + @VirtualPath + + @TargetPath EXEC master..xp_cmdshell @MapCmd set @filePath = convert(NVARCHAR(4),year(getdate())) + - + convert(NVARCHAR(2),datepart(week,getdate())) set @filePath =