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

阿尔哦覅和

　　需求介绍：每天备份线上正式库并且把备份文件复制到测试服务器，测试服务器自动把数据库备份文件还原。
　　方案介绍：
　　第1步：在正式库上创建存储过程用来备份数据库和复制到测试服务器，然后新建作业每天定时执行创建的存储过程。
　　第2步：在测试服务器数据库上创建存储过程用来还原数据库，然后新建作业每天定时执行创建的存储过程。
　　准备：
　　在正式服务器上共享备份文件的文件夹，并且给文件夹everyone的权限。
　　在测试服务器上创建共享文件夹，并且给文件夹everyone的权限。
　　正式库备份数据库和复制数据库代码：



--开启 xp_cmdshell
sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go



USE [master]
GO
/****** Object:  StoredProcedure [dbo].[backup_db_ksedu]    Script Date: 11/22/2016 08:41:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--备份数据库
Create proc [dbo].[backup_db_ksedu]
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='数据库名'
--定义本地备份文件的名称
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='ZONGHEGUANLI-SQ\Administrator'
--设置远程服务器登录密码
Set @strPsw='abc123'
--设置远程服务器连接
Set @strCmdShell= 'net use \\10.150.3.204\db_backup ' + @strPsw + ' /user:' +@strUsr
--设置本地备份文件名称
Set @FullFileName='e:\bak\'+@FileFlag+'.bak'
--设置本地1天前的备份文件名称
--set @FullFileName1='D:\Backup\'+@FileFlag3+'.bak'
--设置远程服务器3天前的备份文件名称
--set @FullFileName2='\\172.17.1.10\j$\db_backup\'+@FileFlag2+'.BAK'
--设置远程服务器保存备份文件目录
Set @ToFileName='\\10.150.3.204\db_backup\'
--Set @ToFileName='e:\Backup\'
--设置为True时,即删除备份,设置为False时,即不删除备份文件
--Set @FlagDel='False'
--设置从本地复制备份文件至远程服务器的语句
Set @SQLStr='copy '+@FullFileName+' '+@ToFileName
--设置删除本地1天前的备份文件
--Set @SQLStr2='del ' +@FullFileName1
--设置删除远程服务器3天前的备份文件
--Set @SQLStr3='del ' +@FullFileName2
--备份数据库 压缩备份
BACKUP DATABASE @strDataBaseName  TO DISK = @FullFileName  WITH INIT, STATS = 20  
--连接远程服务器
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
　　测试库还原数据库代码：



USE [master]
GO
/****** Object:  StoredProcedure [dbo].[restore_db_ksedu]    Script Date: 11/22/2016 08:40:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[restore_db_ksedu]
AS
Declare @strDataBaseName varchar(50)
Declare @FileFlag varchar(50)
--定义备份的数据库名称
Set @strDataBaseName='数据库名'
Set @FileFlag='d:\db_backup\'+@strDataBaseName + '_db_' + replace(convert(char(20),getdate(),112),' ','')+'.bak'
--加上这句防止数据库正在使用导致执行不成功
ALTER DATABASE [数据库名] SET OFFLINE WITH ROLLBACK IMMEDIATE
restore database [数据库名]
from disk=@FileFlag  with file=1,
REPLACE,
recovery  
ALTER  database  [数据库名]  set   online  
　　创建作业计划：参考 http://wenku.baidu.com/link?url=HLojwfVr1gkEuKjhk3Twsy7SxTWNMxovLg4LXpqvg_a3r50XQE5hZt8e03uPhbW8qUNjEx0IKa39DjyJ0spuNN1TV42UCIAMNDUQllhjsZu
　　参考文章：http://www.cnblogs.com/wjj327342306/p/4568177.html