数据库备份过程 完整/差异/日志 过程.

--有了下面三个过程,可以根据需要,设置作业调度备份的频率.

  --用我对应文章中的恢复方法进行恢复.

Create proc [dbo].[usp_BackupFullDatabase]

as
--数据库完整备份
set nocount on
--lichengyi20091123 

declare @dt varchar(12)
select @dt=convert(varchar,getdate(),112)--+convert(varchar,DATEPART (hour,getdate()))


declare @backpath varchar(50) 
set @backpath='d:\databak\'+@dt+'FULLBAK'
declare @mdpath varchar(50)
set @mdpath='MD '+@backpath
exec xp_cmdshell @mdpath


--use command ,backup database 
declare @sql varchar(max)
set @sql =''
select @sql=@sql+' backup database '+name +' to disk='''+@backpath+'\'+name+'.bak'' with format' +char(13)
 from  sys.databases where-- name='FNMDB'
 name not in('master','tempdb','model','msdb','gwData2','OMDB')
and source_database_id is null

exec (@sql)

go

Create proc [dbo].[usp_BackupDiffDatabase]
as
---数据库差异备份
set nocount on
--lichengyi20100728
declare @dt varchar(12)
--select @dt=convert(varchar,getdate(),112)+convert(varchar,DATEPART (HH,getdate()))+convert(varchar,DATEPART (MI,getdate()))
select @dt=convert(varchar,getdate(),112)+'DIFF'
declare @tm varchar(12)
set @tm=convert(varchar(2),DATEPART (HH,getdate()))+convert(varchar(2),DATEPART (MI,getdate()))
declare @backpath varchar(50) 
set @backpath='d:\databak\'+@dt
declare @mdpath varchar(50)
set @mdpath='MD '+@backpath
exec xp_cmdshell @mdpath
--print @mdpath
--use command ,backup database 
declare @sql varchar(max)
set @sql =''
select @sql=@sql+' backup database '+name +' to disk='''+@backpath+'\'+name+@tm+'diff.bak'' WITH FORMAT,DIFFERENTIAL' +char(13)
 from  sys.databases where-- name='FNMDB'
  name not in('master','tempdb','model','msdb','gwData2','monitor'


) and source_database_id is null
exec (@sql)

Create proc  [dbo].[usp_BackupDatabaseLogs]
as
---数据库日志备份
set nocount on
--lichengyi20100728
declare @dt varchar(12)
--select @dt=convert(varchar,getdate(),112)+convert(varchar,DATEPART (HH,getdate()))+convert(varchar,DATEPART (MI,getdate()))
select @dt=convert(varchar,getdate(),112)+'Logs'
declare @tm varchar(12)
set @tm=convert(varchar(2),DATEPART (HH,getdate()))+convert(varchar(2),DATEPART (MI,getdate()))
declare @backpath varchar(50) 
set @backpath='d:\databak\'+@dt
declare @mdpath varchar(50)
set @mdpath='MD '+@backpath
exec xp_cmdshell @mdpath
--print @mdpath
--use command ,backup database 
declare @sql varchar(max)
set @sql =''
select @sql=@sql+' backup LOG '+name +' to disk='''+@backpath+'\'+name+@tm+'.log'' WITH FORMAT' +char(13)
 from  sys.databases 
where   name not in('master','tempdb','model','msdb','gwData2','monitor') and source_database_id is null
exec (@sql)