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

时间:2023-03-08 15:51:59
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=''

 --设置远程服务器连接

 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\.txt  \\192.168.1.2\IWFSBACKUP

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

1.新建作业

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

2.新建步骤

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

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

3.新建计划

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

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

若执行存储过程报错: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