生成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 <> 'master' and @databasename <>'msdb')

begin

--backup log with truncate_only

select @filename = 'c:sqlbackup'+@databasename+'log.back'

select @cmd = ' BACKUP LOG ['+@databasename+'] TO DISK = N'''+@filename+''' WITH INIT , NOUNLOAD , NAME = N'''+@filename +'LOG Backup'', NOSKIP , STATS = 10, NOFORMAT '

exec (@cmd)

select @cmd = 'backup log ['+@databasename+'] with truncate_only'

exec (@cmd)

end

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

http://blog.csdn.net/downmoon/archive/2007/06/05/1639398.aspx