生成MSSQL Data备份的存储过程(downmoon)

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 = ' BACKUPDATABASE[ ' + @databasename + ' ]TODISK=N ''' + @filename + ' .bak '' WITHINIT,NOUNLOAD,NAME=N ''' + @filename + ' 备份 '' ,SKIP,STATS=10,FORMAT '
exec ( @cmd )
if ( @databasename <> ' master ' and @databasename <> ' msdb ' )
begin
-- backuplogwithtruncate_only
select @filename = ' c:/sqlbackup ' + @databasename + ' log.back '

select @cmd = ' BACKUPLOG[ ' + @databasename + ' ]TODISK=N ''' + @filename + ''' WITHINIT,NOUNLOAD,NAME=N ''' + @filename + ' LOGBackup '' ,NOSKIP,STATS=10,NOFORMAT '
exec ( @cmd )
select @cmd = ' backuplog[ ' + @databasename + ' ]withtruncate_only '
exec ( @cmd )
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO