close
自動備份SQL Server資料庫中用戶創建的Stored Procedures為了避免意外丟失/損壞辛苦創建的Stored Procedures，或者想恢復到以前版本的Stored Procedures，這樣提供了一個有效方法，可以自動將指定資料庫 中的Stored Procedures進行備份 。 1. 在特定的資料 庫（建議為SQL 房屋買賣Server的master資料庫）上創建資料表StoredProceduresBackup，用來保存 備份的Stored Procedures。 IF OBJECT_ID('StoredProceduresBackup') IS NOT NULL DROP TABLE StoredProceduresBackup GO CREATE TABLE StoredProceduresBackup ( AutoID INTEGER 信用卡代償IDENTITY(1,1) PRIMARY KEY, InsertDate DATETIME DEFAULT GETDATE(), DatabaseName VARCHAR(50), ProcedureName VARCHAR(50), ProcedureText VARCHAR(4000) ) GO 2. 創建Stored Procedure名為usp _ProceduresVersion，該Stored Procedure用來將需要備份Stored 個人信貸Procedures的備份到上述創建的資料表中。 其中主要訪問sysobjects和syscomments系統表： (1) sysobjects system tableContains one row for each object (constraint, default, log, rule, stored procedure, and so on) created wit hin a database. In tempdb only , this 房屋二胎table includes a row for each temporary object. (2) syscomments system tableContains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. The text column contains the original SQL definition 西服statements, which are limited to a maximum size of 4 MB. This table is stored in each database. (3) source script of stored procedure. /* Name: usp_ProceduresVersion Description: Back up user defined stored-procedures Author: Rickie Modification Log: 酒店經紀NO Description Date Changed By Created procedure 8/27/2004 Rickie */ CREATE PROCEDURE usp_ProceduresVersion @DatabaseName NVARCHAR(50) AS SET NOCOUNT ON --This will hold the dynamic string. DECLARE 禮服@strSQL NVARCHAR(4000) --Set the string --Only stored procedures SET @strSQL = 'INSERT INTO master.dbo.StoredProceduresBackup( DatabaseName,ProcedureName,ProcedureText ) SELECT ''' + @DatabaseName + ''', so.name, 禮服sc.text FROM ' + @DatabaseName + '.dbo.sysobjects so INNER JOIN ' + @DatabaseName + '.dbo.syscomments sc ON so.id = sc.id WHERE so.type = ''p''' + ' and so.status>0 Order By so.id ' --Execute the string 辦公室出租EXEC dbo.sp_executesql @strSQL GO 3. 創建Job執行上述Stored Procedure 在SQL Server 上創建Job，並設定運行計畫，這樣指定資料庫的Stored Procedures就可以自動備份到上述資料表中。


.msgcontent .wsharing ul li { text-indent: 0; }



分享

居酒屋Facebook
Plurk
YAHOO!
arrow
arrow
    全站熱搜

    pl64plzcwu 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄