定期备份数据库存储过程脚本

/*

--创建表

CREATE TABLE procinfo_history(procname VARCHAR(256),texts NVARCHAR(max),tjdate CHAR(10),ver INT DEFAULT 0)

*/

--存储过程导出操作

CREATE PROC backup_procedure

AS

BEGIN

CREATE TABLE #proc( text VARCHAR(max))

DECLARE @tjdate CHAR(10),@ver int

DECLARE @proc NVARCHAR(128)

SELECT @tjdate=CONVERT(CHAR(10),GETDATE(),121)

SELECT @ver=MAX(ver) FROM dbo.procinfo_history

SELECT @ver=ISNULL(@ver,0)+1

DECLARE cur CURSOR FOR SELECT name

FROM sys.objects WHERE type='p' AND schema_id=1

OPEN cur

FETCH NEXT FROM cur INTO @proc

WHILE @@FETCH_STATUS=0

BEGIN

TRUNCATE TABLE #proc

INSERT #proc

EXEC sp_helptext @proc

INSERT dbo.procinfo_history

(

procname,

texts,

tjdate,ver

)

SELECT @proc,text,@tjdate,@ver FROM #proc

FETCH NEXT FROM cur INTO @proc

END

CLOSE cur

DEALLOCATE cur

DROP TABLE #proc

END