您的位置:澳门新葡8455最新网站 > 数据库管理 > 澳门新葡萄京娱乐场Server自动备份存储过程和视

澳门新葡萄京娱乐场Server自动备份存储过程和视

发布时间:2019-12-09 15:19编辑:数据库管理浏览(169)

    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

    本条蕴藏进程在SQLSEHavalVE奥迪Q7代理里成立三个职分,那些任务的陈说都包括在Maint.YourSQLDba_DoMaint存款和储蓄进程里
    任务生机勃勃:在早晨实施YourSQLDba_FullBackups_And_Maintenance 作业,那么些作业的用项是实行每一天维护蕴涵完全备份数据库
    职分二:每15分钟推行YourSQLDba_LogBackups 作业,这些作业的用项是备份专门的学业日志

    YourSQLDba创制贰个数据库邮件profile 命名称叫YourSQLDba_EmailProfile
    其风姿洒脱profile里有一个帐户:名称叫<servername>.<instanceName>@YourSQLDba.com

        <servername> 是您的服务器的微型机名.
        <instanceName> 是你的SQLSE奥迪Q5VEEscort实例名字

    YourSQLDba还或者会创立叁个SQLSETiggoVE奇骏代理操作员,命名叫YourSQLDba_Operator ,那一个操作员满含了@email参数

    YourSQLDba会配置警告依据你安插的数据库邮件,发送警示到您的邮件地址
    YourSQLDba会退换SQLSEENVISIONVELX570实例暗中认可的SQL E奥迪Q5ROEscortLOG的多寡,暗中认可独有7个,那样就能够有越来越长的E大切诺基RO揽胜LOG历史足以查究

     

    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

    3 创建SQL Server 代理 作业

    在SQL Server代理中开创作业,设置为定期奉行存款和储蓄进程proc_backup即可。

     

    本文由澳门新葡8455最新网站发布于数据库管理,转载请注明出处:澳门新葡萄京娱乐场Server自动备份存储过程和视

    关键词: