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

procbackup

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

proc_backup

3 创建sql server 代理 作业

在sql server代理中创建作业，设置为定时执行存储过程proc_backup即可。