SQL Server2005异地自动备份方法

admin MsSql

2023-12-07 0 421

最近公司刚刚搭建了一台安防综合平台，综合平台服务器上安装了SQL Server 2005，由于没有备份服务器，所以只能异地备份数据库了。在网上找了很多的资料，自己整合了一下。

一、开启SQLAgent服务，并设置为“自动”

二、备份

1、SQL代理-作业-新建作业，这是在SQL2005下的操作，其实SQL2000也是大同小异（管理→SQLServer代理→右键作业→新建作业）

图1

2、在常规中输入名字

图2

3、“步骤”项—>新建，输入名称，选中数据，输入以下语句，

图3

语句如下：

–在SQL Server2005中cmdshell命令默认是关闭的，首先要开启cmdshell命令：

–显示高级选项（仅需执行一次） EXEC sp_configure \’show advanced options\’, 1 GO RECONFIGURE GO –允许执行xp_cmdshell EXEC sp_configure \’xp_cmdshell\’, 1 GO RECONFIGURE GO

declare @sql varchar(4000) declare @backupfile varchar(2000) declare @now datetime declare @retaindays int declare @deletefiles varchar(2000) declare @cmd varchar(2000) declare @i int declare @User varchar(2000) declare @Pwd varchar(2000) declare @Store varchar(2000) declare @IPPart varchar(2000) declare @IP varchar(2000) ————-以下自行修改———– set @Store=\’EmployeeInfo\’ ——–数据库名———– set @User =\’liuhs\’ ———-用户名———-要管理员权限 set @Pwd =\’winter\’ ———-密码———-密码不要有符号 set @IPPart=\’d$\\back\’ —–路径—– set @IP=\’192.168.3.42\’ —–服务器IP或主机名– set @retaindays=1 —要保留备份的天数 ————以上自行修改———— –添加映射驱动器 declare @string nvarchar(200) set @string = \’net use z: \\\\\’+@IP+\’\\\’+@IPPart+\’ \”winter\” /user:hp-liuhs\\\’+@User exec master..xp_cmdshell @string set @now=getdate() —删除以前的备份，这个还没有实现好，有时间再修改了 set @i=0 while (@i < 30) begin set @deletefiles = \’z:\\data\’+@Store+ \’_db_\’ +convert(varchar(8),dateadd(dd,-@retaindays- @i,@now),112)++\’.BAK\’ set @cmd=\’del \’ + @deletefiles exec master..xp_cmdshell @cmd set @i = @i +1end—-开始备份set @backupfile=\’z:\\data\’+ @Store + \’_db_\’+ replace(replace(replace(convert(varchar,getdate(),20),\’-\’,\’\’),\’ \’,\’\’),\’:\’,\’\’)+\’.BAK\’set @sql=\’backup database \’ + @Store + \’ to disk=\’\’\’+@backupfile+\’\’\’ with retaindays=\’+convert(varchar(10),@retaindays)exec (@sql)set @backupfile=\’z:\\data\’+ @Store +\’_tlog_\’+replace(replace(replace(convert(varchar,getdate(),20),\’-\’,\’\’),\’ \’,\’\’),\’:\’,\’\’)+\’.TRN\’set @sql=\’backup LOG \’ + @Store + \’ TO DISK=\’\’\’+@backupfile+\’\’\’ with retaindays=\’+convert(varchar(10),@retaindays)exec (@sql)—删除映射exec master..xp_cmdshell \’net use z: /delete\’

4、新建作业计划，在指定时间内备份