1.完全備份數(shù)據(jù)的腳本，可以每天執(zhí)行

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'						--備份時(shí)顯示的名稱
select @dbName='BOM'						--數(shù)據(jù)庫(kù)名稱
select @pathLocal='E:\DBBACK\'				--本地的備份路徑
select @pathRemote='\\192.168.0.100\d$\DATA\BOM\'	--網(wǎng)絡(luò)上的備份路徑
select @KeepDayLocal=-3						--本地的備份文件存放的時(shí)間,-3表示超過三天就刪除
select @KeepDayRemote=-5					--遠(yuǎn)程的備份文件存放的時(shí)間,-5表示超過五天就刪除
select @bCopyDatatoRemote=1					--是否復(fù)制完整的備份文件到遠(yuǎn)程機(jī)器上
select @bCopyLogtoRemote=1					--是否復(fù)制事務(wù)日志備份到遠(yuǎn)程機(jī)器上
select @bDeleteLocalData=1					--是否刪除本地的過期的備份文件
select @bDeleteRemoteData=1					--是否刪除遠(yuǎn)程機(jī)器上的過期的備份文件
--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.每小時(shí)備份事務(wù)日志的腳本

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