生成MSSQL Data备份的存储过程

SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO

ALTERPROCEDURE CPP_BackDataBase@databasename varchar(100)) ASdeclare @filename varchar(100),@cmd varchar(255)select @databasename = rtrim(@databasename)select @filename = 'c:sqlbackup'+ rtrim(convert(varchar(10),getdate(),20))set @cmd = 'md '+@filenameexec master.dbo.xp_cmdshell @cmdselect @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)endGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

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