用存储过程实现MSSQL数据的网络异地备份

  MSSQL的异地远程存储，SQLServer备到FileServer上
环境： win2k+sqlserver 2K SP3

 backup database msdb to disk=’computer estfilename.bak’ --（注意大小写）

如果SQL异地备份失败，归根结底是权限问题！ H

 那么你的SQLServer的启动用户必须在FileServer上有足够的权限!

1、SQLServer上新建一SQLUser用户权限大一点。

2、FileServer上建同一用户对某一文件夹有足够权限，就是在两台机器上建相同的用户名和密码，然后与这个用户名登入电脑。

3、两机的SQLUser密码相同(方便一点)
4、将SQLServer改为SQLUser启动
   (管理工具-->服务-->mssql项-->属性-->指定用户及密码，是计算机的登入用户名)

5、backup database 数据库 to disk=’192.168.*.*文件夹ShareBak.Bak’就可以了。

作业：db_backup2pc @databaseName='databaseName',@filepath='filepath'

CREATE   PROCEDURE  dbo.db_backup2pc

@databaseName    nvarchar ( 100 )  =   null ,  -- 数据库名
@filepath                 nvarchar ( 125 )  =   null    -- 文件保存路径(IP+隐藏共享+)

AS
BEGIN
DECLARE
  @year1     varchar ( 4 ),
  @month1     varchar ( 2 ),
  @day1     varchar ( 2 ),
  @flag     varchar ( 255 ),
  @proc_result   tinyint ,   /**/ /*返回系统存储过程xp_cmdshell运行结果*/
  @sqlstr     varchar ( 2000 ),
  @createdir   varchar ( 255 )    /**/ /*建立文件备份的目录*/

begin
             
--  Get year & month &day fromat of the day before yesterday
--  SET @year1 = substring(convert(varchar,datepart(yyyy,getdate()-2)),3,2)
  SET   @month1   = substring ( convert ( varchar , datepart (mm, getdate ())), 1 , 2 )
  SET   @day1 = substring ( convert ( varchar , datepart (dd, getdate ())), 1 , 2 ) 
  --  if len(@month1)<2 set @month1 = '0' + @month1 /* 不足两位的前面加零 */
  --  if len(@day1)<2 set @day1 = '0' + @day1 
 
  set   @sqlstr = ' dir  ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1
  EXEC   @proc_result   =  master..xp_cmdshell  @sqlstr ,no_output
 
  if  ( @proc_result <> 0 )  /**/ /*系统存储过程xp_cmdshell返回代码值:0(成功）或1（失败）*/
   begin
    set   @createdir = ' md  ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1    /**/ /*建立文件备份的目录*/
    EXEC  master..xp_cmdshell  @createdir ,no_output

    set   @sqlstr = ' backup database  ' + @databaseName + '  to disk= ''' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' ' + @databaseName + ' .bak '''   -- +' with init'   
    Execute  ( @sqlstr )   /**/ /* 备份databaseName数据 */
   
    set   @sqlstr = ' backup database master to disk= ''' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' master.bak '''
    Execute  ( @sqlstr )   /**/ /* 备份 master 数据 */
   
    set   @sqlstr = ' backup database msdb to disk= ''' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' msdb.bak '''
    Execute  ( @sqlstr )   /**/ /* 备份 msdb 数据 */

    set   @flag = ' Backup database successful. '
   end
  else
   set   @flag = ' The directory " ' + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' " has been in, backup database unsuccessful. '  

  print   @flag   
END
END
GO

 

作业：delete_db_backupfile2pc @databaseName='databaseName',@filepath='filepath'

CREATE   PROCEDURE  dbo.delete_db_backupfile2pc

@databaseName   nvarchar ( 100 ) = null ,
@filepath       nvarchar ( 125 ) = null

AS
DECLARE
  @year1     varchar ( 4 ),
  @month1     varchar ( 2 ),
  @day1     varchar ( 2 ),
  @sqlstr     varchar ( 2000 ),
  @flag   varchar ( 255 ),
  @proc_result   tinyint   /**/ /*返回系统存储过程xp_cmdshell运行结果*/
begin

  if   @databaseName   =   null   or   @filepath   =   null  
  set   @flag = ' Variable Error! '
  else
   begin              

-- 保存7天的数据
  SET   @month1   = substring ( convert ( varchar , datepart (mm, getdate () - 7 )), 1 , 2 )
  SET   @day1 = substring ( convert ( varchar , datepart (dd, getdate () - 7 )), 1 , 2 )

  --  if len(@month1)<2 set @month1 = '0' + @month1 /* 不足两位的前面加零 */
  --  if len(@day1)<2 set @day1 = '0' + @day1 

  set   @sqlstr = ' dir  ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1
  EXEC   @proc_result   =  master..xp_cmdshell  @sqlstr ,no_output

  if  ( @proc_result = 0 )   /**/ /*系统存储过程xp_cmdshell返回代码值:0(成功）或1（失败）*/
   begin
    set   @sqlstr = ' del  ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + '  /q '
    exec  master..xp_cmdshell  @sqlstr ,no_output     -- 删除目录下的文件
    set   @sqlstr = ' rd  ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1
    exec  master..xp_cmdshell  @sqlstr ,no_output    -- 删除目录
    set   @flag = ' Delete file successful！ '
   end
  else
   set   @flag = ' Can not find " ' + @filepath + @databaseName + ' _ ' + @month1 + ' - ' + @day1 + ' " file！ '
  end  
   print   @flag  
end
GO