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

SQL2000关于用代码进行备份等操作-参考/*******************完整备份作业*******************/--完整备份,每周一次USE MasterGOdeclare @strvarchar(100)set @str=D:\DBtext\jgj\DBABak\FullBak+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 @strvarchar(100)set @str=D:\DBtext\jgj\DBABak\DiffBak+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 @strvarchar(100)set @str=D:\DBtext\jgj\DBABak\logbak+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 @strvarchar(100),@dirvarchar(100),@fileNamevarchar(30)set @dir=del D:\DBtext\jgj\DBABak\set @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 上生成的脚本-- 由: HEQUANXIN\Tyler-- 服务器: (LOCAL)BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = ND