新闻作文,情与谁共 19楼,当归的功效与作用及禁忌
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
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
在sql server代理中创建作业,设置为定时执行存储过程proc_backup即可。
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
SQL Server免费版的安装以及使用SQL Server Management Studio(SSMS)连接数据库的图文方法
SQL Server 2017 Developer的下载、安装、配置及SSMS的下载安装配置(图文教程详解)
网友评论