| 订阅 | 在线投稿
分享
 
 
 

通过作业调度建立SQL Server的自动备份

2008-07-12 07:37:33 编辑來源:互联网 国际版 评论
 
 
本文为【通过作业调度建立SQL Server的自动备份】的汉字拼音对照版显示拼音
  SQL Serverdezuoyetiaodulaijianlizidongbeifendefangfa

  1jinruqiyeguanliqizhong->guanli->sql serverdaili->zuoye

  2xinjianzuoyezuoyemingchengsui便bianqulirudatabeifensuoyouzhexuanzesadangranniyekeyixuanzeqitayonghuqiantishigaiyonghuyouzhixingzuoyedequanxian

  3dianjibuzhoubiaoqianjinrubuzhoumianbanxinjianbuzhoubuzhoumingkeyisui便biantianxierubuzhou1leixingheshujukumorenbuxuyaoxiugaiminglingzhongxieruyixiayuju

  BACKUP DATABASE [shujukuming] TO DISK = N'F:\data\shujukubeifen' WITH NOINIT , NOUNLOAD , NAME = N'shujuku beifen', NOSKIP , STATS = 10, NOFORMAT

  zhuyixuyaoxiugaidedifangshujukumingDISKzhelixuyaotianxielujinghenideshujukubeifendemingchenghoumiandeNamekeyisui便biantianxie

  4dianjitiaodubiaoqianjinrutiaodumianbanxinjiantiaodumingchengsui便biantianxiexuanzefanfuchuxiandiangenggaikeyixuanzenixiangyaozhixingrenwudesuiyitiaodurumeitianmei2tianmeixingqimeiyuedenggenjuxuyaozijishezhi

  5quedinghoubuyaowangjiyijianshiqingzainigangcaijianlidegongzuoshangdianyoujianqidonggongzuoruguonidegongzuomeiyouwentijianghuitishizhixingchenggongbingyouxiangduiyingdebei

  fenwenjianzainidecipanshangchuxian

  6huanyouyigechongyaodewentijiushinidesql server agentfuwuqiyijingqidong

  ruguowomenxuyaogenjumeitianderiqilaishengchengyigexindebeifenyi便bianwomenqubiebeifenwenjianzheshiwomenxuyaoxiugaiyixiagangcaidesqlyujushenkaoshili

  declare @filename nvarchar(100)

  set @filename='E:\data_Backup\BIS_data_backup_'+convert(char(10),getdate(),112)+'.bak'

  print @filename

  BACKUP DATABASE [BIS] TO DISK = @filename WITH NOINIT, NOUNLOAD, NAME = N'BIS_data_backup', NOSKIP , STATS = 10, NOFORMAT

  huozheanruxiaxiefa

  DECLARE @BACKFILENAME VARCHAR(200)

  DECLARE @DATE CHAR(10)

  DECLARE @FILENAME VARCHAR(200)

  DECLARE @NAME VARCHAR(200)

  SET @DATE=CONVERT(CHAR(10),GETDATE(),120)

  SET @FILENAME='E:\data_Backup\'

  SET @BACKFILENAME=@FILENAME+@DATE

  SET @NAME='BIS_data_backup'

  BACKUP DATABASE [BIS]

  TO DISK = @BACKFILENAME WITH INIT , NOUNLOAD , NAME = @NAME, NOSKIP , STATS = 10, NOFORMAT

  huozhe

  EXECUTE master.dbo.xp_sqlmaint N'-PlanID 1FAEE7FD-F0CC-4E9C-8100-FC1F9AF39063 -WriteHistory -VrfyBackup -BkUpMedia DISK

  -BkUpDB "E:\data_backup" -BkExt "BAK"'

  juli

  declare @filename nvarchar(100)

  set @filename='E:\data_Backup\BIS_data_backup_'+replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),'

  ',''),':','')+'.bak'

  print @filename

  BACKUP DATABASE [BIS] TO DISK = @filename WITH NOINIT, NOUNLOAD, NAME = N'BIS_data_backup', NOSKIP , STATS = 10, NOFORMAT

  shijianchuobudaimiao

  substring(replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':',''),1,12 )原文
 
 
SQL Server的作业调度来建立自动备份的方法 ◆1、进入企业管理器中->管理->sql server代理->作业; ◆2、新建作业,作业名称随便取,例如:data备份,所有者选择sa,当然你也可以选择其他用户,前提是该用户有执行作业的权限; ◆3、点击步骤标签,进入步骤面板。新建步骤,步骤名可以随便填写,如步骤1,类型和数据库默认,不需要修改。命令中写入以下语句: BACKUP DATABASE [数据库名] TO DISK = N'F:\data\数据库备份' WITH NOINIT , NOUNLOAD , NAME = N'数据库 备份', NOSKIP , STATS = 10, NOFORMAT 注意:需要修改的地方,数据库名,DISK=(这里需要填写路径和你的数据库备份的名称)后面的Name=可以随便填写。 ◆4、点击调度标签,进入调度面板,新建调度,名称随便填写,选择反复出现,点更改可以选择你想要执行任务的随意调度。如每天,每2天,每星期,每月等。根据需要自己设置; ◆5、确定后,不要忘记一件事情,在你刚才建立的工作上点右键,启动工作,如果你的工作没有问题,将会提示执行成功,并有相对应的备 份文件在你的磁盘上出现; ◆6、还有一个重要的问题就是你的sql server agent服务器已经启动。 如果我们需要根据每天的日期来生成一个新的备份,以便我们区别备份文件。这时,我们需要修改一下刚才的sql语句。参考实例: declare @filename nvarchar(100) set @filename='E:\data_Backup\BIS_data_backup_'+convert(char(10),getdate(),112)+'.bak' print @filename BACKUP DATABASE [BIS] TO DISK = @filename WITH NOINIT, NOUNLOAD, NAME = N'BIS_data_backup', NOSKIP , STATS = 10, NOFORMAT 或者按如下写法: DECLARE @BACKFILENAME VARCHAR(200) DECLARE @DATE CHAR(10) DECLARE @FILENAME VARCHAR(200) DECLARE @NAME VARCHAR(200) SET @DATE=CONVERT(CHAR(10),GETDATE(),120) SET @FILENAME='E:\data_Backup\' SET @BACKFILENAME=@FILENAME+@DATE SET @NAME='BIS_data_backup' BACKUP DATABASE [BIS] TO DISK = @BACKFILENAME WITH INIT , NOUNLOAD , NAME = @NAME, NOSKIP , STATS = 10, NOFORMAT 或者: EXECUTE master.dbo.xp_sqlmaint N'-PlanID 1FAEE7FD-F0CC-4E9C-8100-FC1F9AF39063 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "E:\data_backup" -BkExt "BAK"' 举例: declare @filename nvarchar(100) set @filename='E:\data_Backup\BIS_data_backup_'+replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')+'.bak' print @filename BACKUP DATABASE [BIS] TO DISK = @filename WITH NOINIT, NOUNLOAD, NAME = N'BIS_data_backup', NOSKIP , STATS = 10, NOFORMAT 时间戳不带秒: substring(replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':',''),1,12 )
󰈣󰈤
 
 
>>返回首页<<
 为你推荐
 
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 
 热帖排行
 
 
王朝网络微信公众号
微信扫码关注本站公众号wangchaonetcn
 
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
©2005- 王朝网络 版权所有