生成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