SQL Server自動備份存儲過程和視圖的方法

1 建立備份數據表

CREATE TABLE [dbo].[ProcBackup](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [sysname] NOT NULL,
    [db] [nvarchar](50) NULL,
    [obj_id] [int] NULL,
    [create_date] [datetime] NOT NULL,
    [modify_date] [datetime] NOT NULL,
    [text] [nvarchar](4000) NULL,
    [type] [nvarchar](5) NULL,
    [remark] [nvarchar](500) NULL,
    [backup_date] [datetime] NULL
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自增ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'名稱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所在資料庫' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'db'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系統對象id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'obj_id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'創建時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'create_date'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'modify_date'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'內容' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'text'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'類型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'type'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'備註' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'remark'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'記錄時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'backup_date'
GO

2 創建存儲過程

create proc proc_backup
as
--插入新增的存儲過程
insert into ProcBackup 
select A.name,'db_' as db,A.[object_id] as obj_id,A.create_date,A.modify_date,C.[text],'P' as type,'' as remark,getdate() as backup_date from sys.procedures A 
left join sys.syscomments C on A.[object_id] = C.id 
where A.name not in (select name from ProcBackup) 

--插入修改過的存儲過程
insert into ProcBackup 
select A.name,'db_' as db,A.[object_id] as obj_id,A.create_date,A.modify_date,C.[text],'P' as type,'' as remark,getdate() as backup_date from sys.procedures A 
left join ProcBackup B on A.[object_id] = B.obj_id 
left join sys.syscomments C on A.[object_id] = C.id 
where A.modify_date > B.modify_date

--插入新增的視圖
insert into ProcBackup 
select A.name,'db_' as db,A.[object_id] as obj_id,A.create_date,A.modify_date,C.[text],'V' as type,'' as remark,getdate() as backup_date from sys.views A 
left join sys.syscomments C on A.[object_id] = C.id 
where A.name not in (select name from ProcBackup) 

--插入修改過的視圖
insert into ProcBackup 
select A.name,'db_' as db,A.[object_id] as obj_id,A.create_date,A.modify_date,C.[text],'V' as type,'' as remark,getdate() as backup_date from sys.views A 
left join ProcBackup B on A.[object_id] = B.obj_id 
left join sys.syscomments C on A.[object_id] = C.id 
where A.modify_date > B.modify_date

3 創建SQL Server 代理 作業

在SQL Server代理中創建作業，設置為定時執行存儲過程proc_backup即可。