mysqlbackup_full.txt

平时

1.完全备份数据的脚本，可以每天执行

eclare @sql as varchar(200)				--
declare @pathLocal as varchar(500)			--Local path for place backup file
declare @pathRemote as varchar(500)			--Remote path for backup file
declare @Model as varchar(20)				--Model
declare @DBName as varchar(20)				--Database Name
declare @charTemp as varchar(500)			--Temp variant
declare @KeepDayLocal as int				--the period of local file keeping
declare @KeepDayRemote as int				--the period of remote file keeping
declare @bCopyDatatoRemote as bit			--Whether copy data to remote server
declare @bCopyLogtoRemote as bit			--whether copy log to remote server
declare @bDeleteLocalData as bit			--Whether delete local data file which over time
declare @bDeleteRemoteData as bit			--Whether delete remote data file which over time
--Set variants
select @model='BOM'						--备份时显示的名称
select @dbName='BOM'						--数据库名称
select @pathLocal='E:\DBBACK\'				--本地的备份路径
select @pathRemote='\\192.168.0.100\d$\DATA\BOM\'	--网络上的备份路径
select @KeepDayLocal=-3						--本地的备份文件存放的时间,-3表示超过三天就删除
select @KeepDayRemote=-5					--远程的备份文件存放的时间,-5表示超过五天就删除
select @bCopyDatatoRemote=1					--是否复制完整的备份文件到远程机器上
select @bCopyLogtoRemote=1					--是否复制事务日志备份到远程机器上
select @bDeleteLocalData=1					--是否删除本地的过期的备份文件
select @bDeleteRemoteData=1					--是否删除远程机器上的过期的备份文件
--start Backup
--1.Backup data to local disk completely
select @sql='backup database '+@dbName+' to DISK='''+@pathLocal+@model+convert(varchar(20),getdate(),12)+'.bak'''
execute sp_sqlexec @sql
--2.copy backup file to remote server
if (@bCopyDatatoRemote=1)
begin
	select @sql=@Model+convert(varchar(200),getdate(),12)+'.bak'
	select @sql='copy '+@pathLocal+@sql+' '+@pathRemote+@sql
	exec master..xp_cmdshell @sql
end
--3.Copy log backup file to remote server
if (@bCopyLogtoRemote=1)
begin
	select @sql=@Model+'Log.bak'
	select @sql='copy /y '+@pathLocal+@sql+' '+@pathRemote+@sql+convert(varchar(200),dateadd(d,-1,getdate()),12)+'.bak'
	exec master..xp_cmdshell @sql
end
--4.Init log backup file
select @sql='Backup log '+@dbName+' to DISK='''+@pathLocal+@model+'LOG.bak'''+' with init'
exec (@sql)
--5.Delete backup file which over time
if (@bDeleteLocalData=1)
begin
	select @sql=@Model+convert(varchar(200),dateadd(d,@keepDayLocal,getdate()),12)+'.bak'
	select @sql='del '+@pathLocal+@sql
	exec master..xp_cmdshell @sql
end
--6.Delete backup file which over time on remote server
if (@bDeleteRemoteData=1)
begin
	select @sql=@model+convert(varchar(200),dateadd(d,@KeepDayRemote,getdate()),12)+'.bak'
	select @sql=' del  '+@pathRemote+@sql
	exec master..xp_cmdshell @sql
	select @sql=@Model+'log.bak'+convert(varchar(200),dateadd(d,@KeepDayRemote,getdate()),12)+'.bak'
	select @sql=' del  '+@pathLocal+@sql
	exec master..xp_cmdshell @sql
end


2.每小时备份事务日志的脚本

eclare @sql as varchar(200)				--
declare @pathLocal as varchar(500)			--Local path for place backup file
declare @pathRemote as varchar(500)			--Remote path for backup file
declare @Model as varchar(20)				--Model
declare @DBName as varchar(20)				--Database Name
declare @charTemp as varchar(500)			--Temp variant
declare @KeepDayLocal as int				--the period of local file keeping
declare @KeepDayRemote as int				--the period of remote file keeping
declare @bCopyDatatoRemote as bit			--Whether copy data to remote server
declare @bCopyLogtoRemote as bit			--whether copy log to remote server
declare @bDeleteLocalData as bit			--Whether delete local data file which over time
declare @bDeleteRemoteData as bit			--Whether delete remote data file which over time
--Set variants
select @model='BOM'
select @dbName='BOM'
select @pathLocal='E:\DBBACK\'
select @pathRemote='\\192.168.0.100\d$\DATA\BOM\'
select @KeepDayLocal=-5
select @KeepDayRemote=-20
select @bCopyDatatoRemote=1
select @bCopyLogtoRemote=1
select @bDeleteLocalData=1
select @bDeleteRemoteData=1

select @sql='backup log '+@dbName+' to disk='''+@pathLocal+@model+'LOG.bak'''
exec (@sql)

select @sql=@Model+'log.bak'
select @sql='copy /y '+@pathLocal+@sql+' '+@pathRemote+@sql
exec master..xp_cmdshell @sql