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

《SQL2000关于用代码进行备份等操作》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、SQL2000关于用代码进行备份等操作-参考/*******************完整备份作业*******************/--完整备份,每周一次USEMasterGOdeclare@strvarchar(100)set@str='D:DBtextjgjDBABakFullBak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),'',''),':','')+'.bak'BACKUPDATABASE[demo]TODISK=@strWITHRETAINDAYS=1

2、5,NOFORMAT,NOINIT,NAME=N'Demo完整备份',SKIP,NOREWIND,NOUNLOAD,STATS=10GO/*******************差异备份作业*******************/--截断日志USEMasterGOBACKUPLOGDemoWITHNO_LOGGO--收缩日志文件USEDemoGODBCCSHRINKFILE(N'Demo_log',0,TRUNCATEONLY)GO--差异备份,每天一次USEMasterGOdeclare@strvarchar(100)set@str='D:DBtextj

3、gjDBABakDiffBak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),'',''),':','')+'.diff'BACKUPDATABASE[Demo]TODISK=@strWITHDIFFERENTIAL,RETAINDAYS=8,NOFORMAT,NOINIT,NAME=N'Demo差异备份',SKIP,NOREWIND,NOUNLOAD,STATS=10GO/******************日志备份作业*******************/--日志备份,

4、每小时一次USEDemoGOdeclare@strvarchar(100)set@str='D:DBtextjgjDBABaklogbak'+replace(replace(replace(convert(varchar,getdate(),20),'-',''),'',''),':','')+'.trn'BACKUPLOG[Demo]TODISK=@strWITHRETAINDAYS=3,NOFORMAT,NOINIT,NAME=N'Demo日志备份',SKIP,NOREWIND,NOUNLOAD,STATS=10GO--删除过期的备份文件,每天两

5、次declare@strvarchar(100),@dirvarchar(100),@fileNamevarchar(30)set@dir='delD:DBtextjgjDBABak'set@filename=left(replace(replace(replace(convert(varchar,getdate()-15,20),'-',''),'',''),':',''),8)set@str=@dir+'fullbak'+@filename+'*.bak'execxp_cmdshell@strset@filename=left(replace(r

6、eplace(replace(convert(varchar,getdate()-8,20),'-',''),'',''),':',''),8)set@str=@dir+'diffbak'+@filename+'*.diff'execxp_cmdshell@strset@filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),'-',''),'',''),':',''),8)set@str=@dir+'logbak'+@filename+'*.trn'execxp_cmdsh

7、ell@str===============================================================================================================SQL企业管理器提取的SQL脚本--2012-02-22/10:10上生成的脚本--由:HEQUANXINTyler--服务器:(LOCAL)BEGINTRANSACTIONDECLARE@JobIDBINARY(16)DECLARE@ReturnCodeINTSELECT@ReturnCode=0IF(SELECTCOUN

8、T(*)FROMmsdb.dbo.syscatego