生成MSSQL Data备份的存储过程

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


ALTER  PROCEDURE CPP_BackDataBase@databasename varchar(100)) AS
declare @filename varchar(100),@cmd varchar(255)
select @databasename = rtrim(@databasename)
select @filename = 'c:sqlbackup'+ rtrim(convert(varchar(10),getdate(),20))
set @cmd = 'md '+@filename
exec master.dbo.xp_cmdshell @cmd
select @filename = @filename +''+@databasename + rtrim(convert(varchar(10),getdate(),20))
select @filename = ltrim(rtrim(@filename))
select @cmd ='BACKUP DATABASE['+@databasename+'] TO DISK = N'''+@filename +'.bak'' WITH INIT , NOUNLOAD , NAME = N''' +@filename+'备份'', SKIP , STATS = 10, FORMAT '
exec (@cmd)
if(@databasename <> &#39;master&#39; and @databasename <>&#39;msdb&#39;)
begin
  --backup log with truncate_only
  select @filename = &#39;c:sqlbackup&#39;+@databasename+&#39;log.back&#39;

  select @cmd = &#39; BACKUP LOG [&#39;+@databasename+&#39;] TO DISK = N&#39;&#39;&#39;+@filename+&#39;&#39;&#39; WITH INIT , NOUNLOAD , NAME = N&#39;&#39;&#39;+@filename +&#39;LOG Backup&#39;&#39;, NOSKIP , STATS = 10, NOFORMAT &#39;
  exec (@cmd)
  select @cmd = &#39;backup log [&#39;+@databasename+&#39;] with truncate_only&#39;
  exec (@cmd)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO