定时备份SQL SERVER的数据库并且把备份文件复制到另外一台服务器保存

 最近公司的一台SQL SERVER服务器需要每天进行备份，备份完后需要把备份文件复制到另外一台服务器的其中一个磁盘进行保存，备份文件在本地服务器保留一天，一天后的自动删除，远程服务器的备份文件保存3天，3天后的文件自动删除。

环境：
A服务器：windown 2008 server   SQL Server2008
B服务器：windown 2000 server   SQL Server2000

A服务器的SQL SERVER里面有三个数据库需要备份的: A1、A2、A3   （每天进行全备，备份为：D:\Backup），备份完后需要把这些文件物理拷备至B服务器的 J 盘下在目录db_backup下保存3天，3天后即删除，A服务器的备份文件只保留1天，1天后即删除。

实现方法：（目前自己找到的两种方法）
1、在A服务器上为数据库A1、A2、A3创建"维护计划"，在维护计划里定义执行每天进行对三个库进行全库备份，备份完后，在A服务器创建shell脚本，通过windows的“任务计划程序”来定时copy文件至服务器B的J盘db_backup目录保存。
2、在A服务器上的SQL SERVER的系统数据库master创建一个存储过程，存储过程的功能包括备份A1、A2、A3数据库，并且在执行完备份后，把备份文件复制到服务器B保存，然后再通过判断条件，定时删除服务器A与服务器B上的备份文件。

在这里我们选择第二种方法来实现，步骤如下：
首先要测试两台服务器的的连通性，使用ping 命令测试一下A与B相互之间是否能成功ping通，另外在A、B服务器上，检查服务Computer Browser、Server、Workstation服务是否成功启动，端口139、445是否有开启，如果没有的话，手动启动服务以及开启139、445的监听端口，另外还有一点，要保证两台服务器的“Microsoft的网络的文件和打印机共享”服务有在网络连接处勾选，如下:

上述条件都满足后，我们可以先测试一下两台机是否能正常copy文件，可以先在服务器A的其中一盘，比如D:\Backup目录下创建一个1.txt的文本文件，把这个文件拷贝至服务器B的 j:\db_backup目录内，首先我们在服务器A打开命令行，开始-->运行-->CMD,这里我们通过net use来创建服务器A与服务器B的连接:
1.在服务器A端，查看默认的共享有哪些，查看系统默认的共享以及当前用户设置的共享名称及路径，如下

红色框的表示，服务器A目前设置的共享名称，后面跟是的共享路径，这里我们需要把d:\Backup设置成共享，共享名为Database，执行如下命令：
net share Database=d:\Backup ，执行完后，再执行net share就可以看到刚才的共享设置是否成功。

(注意，设置文件夹共享之前，要把D盘先给共享出来，才能执行上面的这些步骤，共享D盘，net share d$=d:\)

2、同样在服务器B也需要设置共享目录，B的共享目录为： j:\db_backup 共享名为newbackup$(共享名可以随意取，不要与现有的共享名同名即可)，同样共享文件夹之前，需要先共享J盘，不然共享目录是不会成功的，由于B用的是windows 2000 server，我们先手动把 j 盘设置成共享，如下：

net share j$=j:\

共享完成后，我们再共享j:\db_backup

net share newbackup$=j:\db_backup

共享完成后，我们即可在服务器A端测试copy命令是否能正常执行了，如下：

copy d:\Backup\1.txt  \\172.17.1.10\newbackup$\   (中间有空格)     172.17.1.10为服务器B的IP地址

如果能复制成功，说明我们之前的配置成功了，就可以继续往下执行了，如果不通，请仔细检查设置、服务、端口、网络等等。


3、在服务器A上的SQL SERVER的master创建如下存储过程:
代码如下：
 

CREATE proc Backup_DataBase_A1

as  

Declare @strPsw varchar(50)

Declare @strUsr varchar(50) 

Declare @strCmdShell varchar(300)

Declare @strDataBaseName varchar(20)

Declare @FullFileName Varchar(200)

Declare @FullFileName1 Varchar(200)

Declare @FullFileName2 Varchar(200)

Declare @FileFlag varchar(50) 

Declare @FileFlag2 varchar(50) 

DECLARE @FileFlag3 varchar(50)

Declare @ToFileName varchar(200)  

Declare @SQLStr varchar(500)

Declare @SQLStr2 varchar(500)   

Declare @SQLStr3 varchar(500)    

Declare @FlagDel varchar(20)

--定义备份的数据库名称

Set @strDataBaseName='A1'

--定义本地备份文件的名称

Set @FileFlag=@strDataBaseName + '_db_' + replace(convert(char(20),getdate(),112),' ','')

--定义本地1天前的备份文件名称

Set @FileFlag3=@strDataBaseName + '_db_' + replace(convert(char(20),getdate()-1,112),' ','')

--定义远程服务器3天前的备份文件名称

Set @FileFlag2=@strDataBaseName + '_db_' + replace(convert(char(20),getdate()-3,112),' ','')

--设置远程服务器的登录域和用户名

Set @strUsr='db-b7ca68c1260d\Administrator'

--设置远程服务器登录密码

Set @strPsw='abc123'

--设置远程服务器连接

Set @strCmdShell= 'net use \\172.17.1.10\j$ ' + @strPsw + ' /user:' +@strUsr

--设置本地备份文件名称

Set @FullFileName='D:\Backup\'+@FileFlag+'.BAK'

--设置本地1天前的备份文件名称

set @FullFileName1='D:\Backup\'+@FileFlag3+'.BAK'

--设置远程服务器3天前的备份文件名称

set @FullFileName2='\\172.17.1.10\j$\db_backup\'+@FileFlag2+'.BAK'

--设置远程服务器保存备份文件目录

Set @ToFileName='\\172.17.1.10\j$\db_backup\'

--设置为True时,即删除备份,设置为False时,即不删除备份文件

Set @FlagDel='True'

--设置从本地复制备份文件至远程服务器的语句

Set @SQLStr='copy '+@FullFileName+' '+@ToFileName

--设置删除本地1天前的备份文件

Set @SQLStr2='del ' +@FullFileName1 

--设置删除远程服务器3天前的备份文件

Set @SQLStr3='del ' +@FullFileName2

--备份EliteUC数据库 

BackUp DataBase  @strDataBaseName  To Disk=  @FullFileName   with init 

--连接远程服务器 

exec master..xp_cmdshell @strCmdShell

--复制备份文件至远程服务器

exec Master..xp_cmdshell @SQLStr   

--删除1天前本地的备份文件

if (@FlagDel ='True')

exec master.. xp_cmdshell @SQLStr2 

--删除3天前远程服务器备份文件

if (@FlagDel ='True')

exec master.. xp_cmdshell @SQLStr3

go

 

 

4、完成后，再创建一个作业来定时执行上面的存储过程即可，调用存储过程命令如下： 
execute maste.dbo.Backup_DataBase_A1

5、至些，整个实现过程结束，只需要每天去检查备份文件是否有正常删除即可，如果能正常删除，我们就只检查作业每天执行是否成功就可以了。另外，当备份文件比较大时，可以考虑用FTP去传输，传输速度应该比这样直接copy效率会快。

总结：实现此方法的关键是共享目录的设置，以及net use命令的使用，容易出问题的地方也是这两点，所以在配置时，要格外小心。