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

来源:http://www.prospettivedarte.com 作者:计算机教程 人气:98 发布时间:2019-05-11
摘要: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, [tex

1 建立备份数据表

皇牌天下投注网 1皇牌天下投注网 2

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

YourSQLDba的翻译(六)

在运行下面这个存储过程之前,先部署好YourSQLDba,执行YourSQLDba_InstallOrUpdateScript.sql. 脚本。然后运行下面的存储过程进行配置您的YourSQLDba

这个存储过程在SQLSERVER 代理里创建两个任务,这些任务的描述都包含在Maint.YourSQLDba_DoMaint存储过程里
任务一:在午夜执行YourSQLDba_FullBackups_And_Maintenance 作业,这个作业的用途是执行每日维护包括完整备份数据库
任务二:每15分钟执行YourSQLDba_LogBackups 作业,这个作业的用途是备份事务日志

YourSQLDba创建一个数据库邮件profile 命名为YourSQLDba_EmailProfile
这个profile里有一个帐户:名为<servername>.<instanceName>@YourSQLDba.com

    <servername> 是你的服务器的计算机名.
    <instanceName> 是你的SQLSERVER实例名字

YourSQLDba还会创建一个SQLSERVER代理操作员,命名为YourSQLDba_Operator ,这个操作员包含了@email参数

YourSQLDba会配置警报根据你配置的数据库邮件,发送警报到你的邮件地址
YourSQLDba会改变SQLSERVER实例默认的SQL ERRORLOG的数量,默认只有7个,这样就能够有更长的ERRORLOG历史可以追查

 

Initial setup   

Updated : 2010-10-06

Before to run it, deployed YourSQLDba solution by running the script YourSQLDba_InstallOrUpdateScript.sql.

Then run this procedure from YourSQLDba database.

Sample :

 1 Exec YourSQLDba.Install.InitialSetupOfYourSQLDba 
 2   @FullBackupPath = 'c:iSql2005Backups'       -- full backup path destination 
 3 , @LogBackupPath = 'c:iSql2005Backups'   -- log backup path destination 
 4 , @email = 'myAdmin@myDomain.com'              -- Email recipients (or distribution list) 
 5 , @SmtpMailServer = 'myMailServer'             -- Mail server that accept SMTP mail 
 6 
 7 , @ConsecutiveFailedbackupsDaysToPutDbOffline = 0000 
 8                            -- Maximum number of consecutive days of failed full backups allowed 
 9                            -- for a database before putting that database (Offline). 
10                            -- You must choose a value between 4 and 9999. 
11        -- Important: Your can read the explanations in the InitialSetupOfYourSQLDba page 
12        --            for using the @ConsecutiveFailedbackupsDaysToPutDbOffline parameter. 

 


Click here to get the full description of the call to the procedure "Install.InitialSetupOfYourSQLDba".

The procedure create two tasks in SQL Server Agent that invokes the Maint.YourSQLDba_DoMaint procedure. 

At midnight : YourSQLDba_FullBackups_And_Maintenance : Perform daily maintenance including full backups of name :
Every 15 minutes around the clock : YourSQLDba_LogBackups : Does log backups
It creates a database mail profile named YourSQLDba_EmailProfile which contains a account named as

    <servername>.<instanceName>@YourSQLDba.com

    <servername> is the name of the server.
    <instanceName> is the name of the instance if it is not the instance by default.

It creates an SQL Server Agent operator named YourSQLDba_Operator which contains the value of the @email parameter.

It configures SQL Server Agent alert system to have YourSQLDba_EmailProfile.

It modifies log archive number limit on SQL Server instance, so it is possible to have a longer SQL Server Error logs history

 

皇牌天下投注网,2 创建存储过程

皇牌天下投注网 3皇牌天下投注网 4

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

本文由皇牌天下投注网发布于计算机教程,转载请注明出处:SQL Server自动备份存储过程和视图的方法

关键词:

最火资讯