程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 如何生成MSSQL Data備份的存儲過程

如何生成MSSQL Data備份的存儲過程

編輯:關於SqlServer
 

ET 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

 
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved