--Description:备份指定数据到指定路径，第一次完整备份、每月1号完整备份、每周一完整，每天增量备份

--====================================

ALTER procedure [dbo].[BackupDatabase]

@DatabaseName nvarchar(50),

@BackupPath varchar(200),

@IsDelLog int --1表示删除日志

as

declare @sql nvarchar(500);declare @dateName nvarchar(50);

declare @ClareSign int;set @ClareSign=1;

set @dateName='_'+SUBSTRING(replace(CONVERT(char(10),getdate(),21),'-',''),3,6);

if(not exists(select top 1 1 from msdb.dbo.backupset where database_name=@DatabaseName))

begin

 set @sql='BACKUP DATABASE '+@DatabaseName+' TO DISK='''+@BackupPath+'f_'+@DatabaseName+@dateName+'.bak'' WITH FORMAT'

 set @ClareSign=0;

end

else if(DATEPART(DAY,getdate())=1)

begin

 set @sql='BACKUP DATABASE '+@DatabaseName+' TO DISK='''+@BackupPath+'m_'+@DatabaseName+@dateName+'.bak'' WITH FORMAT'

end

else if(DATEPART(weekday,getdate())=2)

begin

 set @sql='BACKUP DATABASE '+@DatabaseName+' TO DISK='''+@BackupPath+'w_'+@DatabaseName+@dateName+'.bak'' WITH FORMAT'

end

else

begin

 set @sql='BACKUP DATABASE '+@DatabaseName+' TO DISK='''+@BackupPath+'d_'+@DatabaseName+@dateName+'.bak'' WITH differential'

 set @ClareSign=0;

end

exec (@sql)

--清除日志

if(@@ERROR=0 and @ClareSign=1 and @IsDelLog=1)

begin

 --简单模式

 exec('ALTER DATABASE '+@DatabaseName +' SET RECOVERY SIMPLE WITH NO_WAIT;ALTER DATABASE '+@DatabaseName +' SET RECOVERY SIMPLE')

 insert into ToolsDB.dbo.TestLog(LogName,Remark) values('备份日志测试',GETDATE())

 declare @FileName nvarchar(50)

 if OBJECT_ID('tempdb.dbo.##bt5') is not null drop table ##bt5 --清除全局临时表

 exec('select name into ##bt5 from '+@DatabaseName+'.dbo.sysfiles where groupid=0')

 declare cursor_name cursor for

 select name from ##bt5

 open cursor_name

 fetch next from cursor_name into @FileName

 while(@@FETCH_STATUS=0)

 begin

 exec('use '+@DatabaseName+' DBCC SHRINKFILE (N'''+@FileName+''' ,1, TRUNCATEONLY)')

 fetch next from cursor_name into @FileName

 end

 close cursor_name

 deallocate cursor_name

 

 --还原为完全模式

 exec('ALTER DATABASE '+ @DatabaseName+' SET RECOVERY FULL WITH NO_WAIT;ALTER DATABASE '+ @DatabaseName+' SET RECOVERY FULL')

 if OBJECT_ID('tempdb.dbo.##bt5') is not null drop table ##bt5 --清除全局临时表

end