欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-12-09 16:55:15
1 建立备份数据表 CREATE TABLE [dbo].[ProcBackup]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [sysname] NOT NULL, [db] [nvarchar](50) NULL, [obj_id] [int] NULL ......

1 建立备份数据表

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]

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 创建存储过程

SQL Server自动备份存储过程和视图的方法
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即可。