if object_id(@c_backupName) is not null
begin
select @c_exesql = 'drop table '+@c_backupName
execute (@c_exesql)
-- 异常检测
select @retcode = @@error,@reccount = @reccount+@@rowcount
if @retcode<>0
begin
update t_sz_backup_tables set status=1,endtime=getdate(),redes='drop table '+@c_backupName+' failed!'
where tableName = @tableName
select @i_errorNum = @i_errorNum +1
select @i_count = @i_count + 1
continue
end
end
-- 创建备份表
select @c_exesql = 'select * into '+@c_backupName +' from '+@tableName +' where 1=2'
execute (@c_exesql)
-- 异常检测
select @retcode = @@error,@reccount = @reccount+@@rowcount
if @retcode <>0
begin
update t_sz_backup_tables set status=1,endtime=getdate(),redes='create table '+@c_backupName+' failed!'
where tableName = @tableName
select @i_errorNum = @i_errorNum +1
select @i_count = @i_count + 1
continue
end
-- 备份数据到历史表
select @c_exesql = 'insert into '+@c_backupName+' select * from '+@tableName
execute (@c_exesql)
-- 异常检测
select @retcode = @@error,@reccount = @reccount+@@rowcount
if @retcode<>0
begin
update t_sz_backup_tables set status=1,endtime=getdate(),redes='insert into '+@c_backupName+' failed!'
where tableName = @tableName
select @i_errorNum = @i_errorNum +1
select @i_count = @i_count + 1
continue
end
-- 执行成功记录日志
update t_sz_backup_tables set status=0,endtime=getdate(),redes='backup into '+@c_backupName+' success!'
where tableName = @tableName
end
else
begin
update t_sz_backup_tables set status = 1,endtime=getdate(),redes = convert(char(20),getdate(),120)+':backup failed!source table '+@tableName+' not found!'
where tableName = @tableName
select @i_errorNum = @i_errorNum + 1
select @i_count = @i_count + 1
continue
end
select @i_count=@i_count+1
end
--*******3.<操作结束,退出>*********************************************************************
select @dt_endtime = getdate()
select @usedtime = datediff(ss,@dt_begintime,@dt_endtime)
if @i_errorNum <>0
begin
select @retcode = 1
select @retdesc = convert(char,@i_errorNum)+' tables backup failed,'+convert(char,
@cursorcount-@i_errorNum)+' backup success,please check up the table t_sz_backup_tables!'
return @retcode
end
else
begin
select @retcode = 0
select @retdesc = convert(varchar,@dt_endtime,120)+'：procedure fn_backup_tables '+convert(char,@cursorcount)+' tables was backuped success！'
return @retcode
end
end
go