SqlServer数据库同时备份到两台服务器上（并自动删除过期文件）

数据库同时备份到两台服务器上（并自动删除过期文件）

举例 ：（本地）服务器A： IP :192.168.1.1

           （远程）服务器B： IP :192.168.1.2

        数据库版本：SqlServer2008R2

一．   两台服务器分别创建共享文件夹（能互相访问，同属一个局域网）

A: E: IWFSBACKUP(文件夹)

B: E: IWFSBACKUP(文件夹)

二．   在master数据库下建立存储过程 dbo.iwfsbackup

 

USE [master]

GO

/****** Object:  StoredProcedure [dbo].[iwfsbackup]    Script Date: 07/17/2018 09:57:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE proc [dbo].[iwfsbackup]

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='db_iwfs'

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

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

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

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

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

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

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

 Set @strUsr='B\administrator'

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

 Set @strPsw='123'

 --设置远程服务器连接

 Set @strCmdShell= 'net use \\192.168.1.2\e$ ' + @strPsw + ' /user:' +@strUsr

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

 Set @FullFileName='E:\IWFSBACKUP\'+@FileFlag+'.BAK'

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

 set @FullFileName1='E:\IWFSBACKUP\'+@FileFlag3+'.BAK'

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

 set @FullFileName2='\\192.168.1.2\e$\IWFSBACKUP\'+@FileFlag2+'.BAK'

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

 Set @ToFileName='\\192.168.1.2\e$\IWFSBACKUP\'

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

 Set @FlagDel='True'

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

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

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

 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  

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

 if (@FlagDel ='True')

 exec master.. xp_cmdshell @SQLStr2

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

 if (@FlagDel ='True')

 exec master.. xp_cmdshell @SQLStr3

 

--作业调用该存储过程

 

execute master.dbo.iwfsbackup

 

--DOS拷贝文件到别的服务器

copy E:\IWFS\1.txt  \\192.168.1.2\IWFSBACKUP

三．   新建SqlServer作业，调用存储过程，执行备份作业

1.新建作业

 

2.新建步骤

 

 

  3.新建计划

 

 

若执行存储过程报错：execute master.dbo.iwfsbackup

“消息 15281，级别 16，状态 1，过程 xp_cmdshell，第 1 行
SQL  Server 阻止了对组件 'xp_cmdshell' 的 过程'sys.xp_cmdshell'  的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用  'xp_cmdshell'。有关启用 'xp_cmdshell' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器". ” 

解决办法：

在master 数据库下执行以下语句：

sp_configure 'show advanced options',1

reconfigure

go

sp_configure 'xp_cmdshell',1

reconfigure

go