declare @today as varchar(20)
declare @year as varchar(20)
declare @month as varchar(20)
declare @day as varchar(20)
declare @path as varchar(100)
set @year=cast(datepart(year,getdate()) as varchar(4))
set @month=right('00'+cast(datepart(month,getdate()) as varchar(2)),2)
set @day=right('00'+(cast(datepart(day,getdate()) as varchar(2))),2)
select @today=@year+@month+@day
set @path='D:\DBBACKUP\'+@today+'.bak'
BACKUP DATABASE [sale] TO DISK = @path WITH INIT , NOUNLOAD , NAME = N'pubs 备份', NOSKIP , STATS = 10, NOFORMAT 在作业里调度就行了!
declare @year as varchar(20)
declare @month as varchar(20)
declare @day as varchar(20)
declare @path as varchar(100)
set @year=cast(datepart(year,getdate()) as varchar(4))
set @month=right('00'+cast(datepart(month,getdate()) as varchar(2)),2)
set @day=right('00'+(cast(datepart(day,getdate()) as varchar(2))),2)
select @today=@year+@month+@day
set @path='D:\DBBACKUP\'+@today+'.bak'
BACKUP DATABASE [sale] TO DISK = @path WITH INIT , NOUNLOAD , NAME = N'pubs 备份', NOSKIP , STATS = 10, NOFORMAT 在作业里调度就行了!
@sBackUpDate datetime, /*备份开始时间*/
@eBackUpDate datetime, /*备份开始时间,包括此时间在内*/
@BackUpTbName varchar(1000), /*备份表名*/
@ColumnName varchar(50) /*标示时间字段*/
as
if (@BackUpTbName is null or @BackUpTbName = '') return /*创建表后缀:备份开始日期*/
declare @TbNamePostfix varchar(150)
set @TbNamePostfix =+ltrim('_')+ltrim(CONVERT(varchar(4), year(@sBackUpDate)))+ltrim(CONVERT(varchar(2), month(@sBackUpDate)))+ltrim(CONVERT(varchar(2), day(@sBackUpDate)))
declare @sdate varchar(10)
declare @edate varchar(10) set @eBackUpDate = dateadd(d,1,@eBackUpDate)
set @sdate = CONVERT(varchar(4), year(@sBackUpDate))+ltrim('-')+ltrim(CONVERT(varchar(2), month(@sBackUpDate)))+ltrim('-')+ltrim(CONVERT(varchar(2), day(@sBackUpDate)))
set @edate = CONVERT(varchar(4), year(@eBackUpDate))+ltrim('-')+ltrim(CONVERT(varchar(2), month(@eBackUpDate)))+ltrim('-')+ltrim(CONVERT(varchar(2), day(@eBackUpDate))) /*备份表名规则为:原表名+表后缀*/
declare @NewBackUpTbName varchar(100)
set @TbNamePostfix =+ltrim('_')+ltrim(CONVERT(varchar(4), year(@sBackUpDate)))+ltrim('_')+ltrim(CONVERT(varchar(2), month(@sBackUpDate)))+ltrim('_')+ltrim(CONVERT(varchar(2), day(@sBackUpDate)))
set @NewBackUpTbName = @BackUpTbName+ltrim(@TbNamePostfix)
declare @execSql Nvarchar(500) declare @isHave int
set @isHave = 0
/*判断备份表是否已经存在*/
set @execSql = ' if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].['+ @NewBackUpTbName +']'')'
set @execSql = @execSql + ' and OBJECTPROPERTY(id, N''IsUserTable'') = 1) set @isHave = 1'
exec SP_EXECUTESQL @execSql, N'@isHave int out',@isHave out if @isHave = 1 return /*备份表已经存在,将反回*/
/*开始备份*/
set @execSql = ''
set @execSql = 'select * into BackUpTableNew.dbo.'+@BackUpTbName +' from dbo.'+@BackUpTbName
set @execSql = @execSql +' where '+ @ColumnName+' >= CONVERT(char(10),'''+ @sdate +''',120)'
set @execSql = @execSql +' and ' + @ColumnName+' <= Dateadd(d,-1,convert(char(10),'''+ @edate +''',120))'
exec SP_EXECUTESQL @execSql
/*删除原表记录*/
set @execSql = ''
set @execSql = 'delete from dbo.'+@BackUpTbName
set @execSql = @execSql +' where '+ @ColumnName+' >= CONVERT(char(10),'''+ @sdate +''',120)'
set @execSql = @execSql +' and ' + @ColumnName+' <= Dateadd(d,-1,convert(char(10),'''+ @edate +''',120))'
exec SP_EXECUTESQL @execSql
GO
CREATE PROCEDURE BackUpTableExec ASdeclare @sdate datetime /*备份开始时间*/
declare @edate datetime /*备份开始时间*/
declare @tbName varchar(100)
declare @cName varchar(50)set @sdate = Dateadd(day,-1,convert(char(10),getdate(),120))
set @edate = convert(char(10),getdate(),120)
set @tbName = 'WapCount'
set @cName = 'createtime'exec BackUpTableNew @sdate,@edate,@tbName,@cName
GO
执行BackUpTableExec,我自己用的一个备份SP你看看吧