SQL Server自动备份存储过程和视图的方法

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]GOEXEC 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'
GOEXEC 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'
GOEXEC 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'
GOEXEC 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'
GOEXEC 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'
GOEXEC 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'
GOEXEC 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'
GOEXEC 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'
GOEXEC 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'
GOEXEC 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

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