SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTERPROCEDURE 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