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

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

2024-08-22 11:06:06   211人阅读

需求介绍：每天备份线上正式库并且把备份文件复制到测试服务器，测试服务器自动把数据库备份文件还原。

方案介绍：

第1步：在正式库上创建存储过程用来备份数据库和复制到测试服务器，然后新建作业每天定时执行创建的存储过程。

第2步：在测试服务器数据库上创建存储过程用来还原数据库，然后新建作业每天定时执行创建的存储过程。

准备：

在正式服务器上共享备份文件的文件夹，并且给文件夹everyone的权限。

在测试服务器上创建共享文件夹，并且给文件夹everyone的权限。

 

正式库备份数据库和复制数据库代码：

--开启 xp_cmdshellsp_configure ‘show advanced options‘,1reconfiguregosp_configure ‘xp_cmdshell‘,1reconfigurego

 

USE [master]GO/****** Object:  StoredProcedure [dbo].[backup_db_ksedu]    Script Date: 11/22/2016 08:41:23 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--备份数据库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 ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[restore_db_ksedu]ASDeclare @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 IMMEDIATErestore 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

 

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


联系
我们