Faça backup automático de procedimentos armazenados criados pelo usuário em um banco de dados SQL Server

Para evitar perder/danificar acidentalmente os Procedimentos Armazenados que você trabalhou tanto para criar, ou se desejar restaurar para uma versão anterior dos Procedimentos Armazenados, isso fornece um método eficaz para fazer backup automático dos Procedimentos Armazenados no arquivo especificado. banco de dados.

1. Crie a tabela de dados StoredProceduresBackup em um banco de dados específico (recomendado para ser o banco de dados mestre do SQL Server) para salvar os procedimentos armazenados de backup.

SE OBJECT_ID('StoredProceduresBackup') NÃO FOR NULO

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. Crie um procedimento armazenado chamado usp_ProceduresVersion. Este procedimento armazenado é usado para fazer backup dos procedimentos armazenados que precisam ser copiados na tabela de dados criada acima.

Entre eles, as tabelas de sistema sysobjects e syscomments são acessadas principalmente:

(1) tabela de sistema sysobjects
Contém uma linha para cada objeto (restrição, padrão, log, regra, procedimento armazenado e assim por diante) criado em um banco de dados somente em tempdb, esta tabela inclui uma linha para cada objeto temporário.

(2) tabela do sistema syscomments
Contém entradas para cada visualização, regra, padrão, acionador, restrição CHECK, restrição DEFAULT e procedimento armazenado. A coluna de texto contém as instruções de definição SQL originais, que são limitadas a um tamanho máximo de 4 MB. .

(3) script de origem do procedimento armazenado.

/*

Nome: usp_ProceduresVersion

Descrição: Backup de procedimentos armazenados definidos pelo usuário

Autor: Rickie

Log de modificação: NÃO

Descrição Data da alteração por

Procedimento criado em 27/08/2004 Rickie

*/

CRIAR PROCEDIMENTO usp_ProceduresVersion @DatabaseName NVARCHAR(50)

AS

SET NOCOUNT ON

--Isso conterá a string dinâmica

DECLARE @strSQL NVARCHAR(4000)

--Definir a string

--Apenas procedimentos armazenados

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''' + ' e so.status>0

Encomendar por so.id '

--Execute a string

EXEC dbo.sp_executesql @strSQL

GO

3. Crie o trabalho e execute o procedimento armazenado acima

para criar o trabalho no SQL Server e definir o plano de execução para que os procedimentos armazenados do banco de dados especificado possam ser automaticamente copiados para a tabela de dados acima.