生成MSSQL Data备份的存储过程

6/6/2007来源:SQL技巧人气:12279

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


ALTER   PROCEDURE [email protected] 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 '[email protected]
exec master.dbo.xp_cmdshell @cmd
select @filename = @filename +''[email protected] + rtrim(convert(varchar(10),getdate(),20))
select @filename = ltrim(rtrim(@filename))
select @cmd ='BACKUP DATABASE['[email protected]+'] TO  DISK = N'''[email protected] +'.bak''  WITH  INIT ,  NOUNLOAD ,  NAME = N''' [email protected]+'备份'',  SKip ,  STATS = 10,  FORMAT '
exec (@cmd)
if(@databasename <> 'master' and @databasename <>'msdb')
begin
    --backup log  with truncate_only
    select @filename = 'c:sqlbackup'[email protected]+'log.back'

    select @cmd = ' BACKUP LOG ['[email protected]+'] TO  DISK = N'''[email protected]+''' WITH  INIT ,  NOUNLOAD ,  NAME = N'''[email protected] +'LOG Backup'',  NOSKIP ,  STATS = 10,  NOFORMAT '
    exec (@cmd)
    select @cmd = 'backup log ['[email protected]+'] 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