您的位置:永利集团登录网址 > 永利集团登录网址 > YourSQLDba的翻译(【永利游戏平台】四)

YourSQLDba的翻译(【永利游戏平台】四)

2019-11-15 10:20

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

 

YourSQLDba的翻译(四)

 

YourSQLDba的每个东西都存储在一个叫 “YourSQLDba”的数据库里

包括:存储过程,内部表(日志,跟踪信息)

YourSQLDba使用SQLSERVER 代理来运行维护作业

有两个作业在创建的时候使用相同的存储过程但是使用不同的参数

一个是每日维护任务(完整性检查 dbcc checkdb,更新统计信息,重组或重建索引,完整备份)
一个是日志备份

当数据库事务日志超过给定的临界值的时候,备份事务日志会重设日志大小

数据库邮件能通知操作员或DBA或负责人

How it does it ?   

Updated : 2009-02-25

Everything is stored in a database named YourSQLDba
Stored procedures
Internal table (logs, and informational tracking info)
It uses SQL Server Agent to run maintenance job
Two job are created that use the same procedure with different parameters
One for daily maintenance (integrity test, update statistics, reorganize or rebuild, full backups)
One for log backups
Job backups reset log size that grow beyond a given threshold
It communicates to operator or Dba or whatever else is responsible (SQL Agent operator) through database mail

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

3 创建SQL Server 代理 作业

在SQL Server代理中创建作业,设置为定时执行存储过程proc_backup即可。

 

本文由永利集团登录网址发布于永利集团登录网址,转载请注明出处:YourSQLDba的翻译(【永利游戏平台】四)

关键词: