 |
|
|
 首页 |
名片设计 CorelDRAW Illustrator AuotoCAD Painter 其他软件 Photoshop Fireworks Flash |
|
为了避免意外丢失/损坏辛劳创建的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 table Contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. In tempdb only, this table includes a row for each temporary object. (2) syscomments system table Contains 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就可以自动备份到上述数据表中。 返回类别: 教程 上一教程: MYSQL数据库函数详解 下一教程: SQL数据操作基础(中级)7 您可以阅读与"自动备份SQL SERVER数据库中用户创建的STORED PROCEDURES"相关的教程: · SQL SERVER数据库定时自动备份 · 设定sql server定期自动备份数据库 · 设定SQL SERVER定期自动备份数据库 · SQL SERVER数据库备份还原另一方式 · SQL SERVER2000中用户自定义数据类型的使用 |
  |
| 快精灵印艺坊 版权所有 |
首页 会员中心在线印刷在线编辑付款方式索取样品设计指南连锁门店
|
||
