YourSQLDba版本升级总结-白红宇

YourSQLDba版本升级总结

阅读量：4570 次

发布时间：2019-06-08

本文共 7256 字，大约阅读时间需要 24 分钟。

在使用YourSQLDba做数据库备份、维护时，像其它软件一样，版本升级是不可避免的。因为YourSQLDba一直在不停更新版本、扩展功能。下面介绍一下升级YourSQLDba时的具体步骤和一些注意事项。下面案例，YourSQLDba原版本为YourSQLDba version: 5.0.2 2012-06-12，升级到YourSQLDba 6.2.5.1。

步骤1： 首先查看服务器对应YourSQLDba的版本信息。因为不同版本的升级可能有所区别。要做的工作可能不一样。

Exec YourSQLDba.Install.PrintVersionInfo
 
========================================
 
YourSQLDba version: 5.0.2 2012-06-12
 
========================================

步骤2：查看YourSQLDba下的所有作业信息。

这个步骤，主要是因为我们在不同程度的扩展了YourSQLDba的一些功能。另外，有可能你对YourSQLDba_FullBackups_And_Maintenance等作业做了一些修改、变更。所以在升级前对变跟的地方有所了解，记录整理，方便升级后做出对应的修改。如果你没有对YourSQLDba做任何修改、扩展，那么可以忽略这个步骤。

使用下面SQL将所有YourSQLDba的作业列出来，然后收集、整理是否有做变跟。是否需要在升级后，做出对应的调整、修改。

SELECT j.job_id                                       AS JOB_ID           
      ,j.name                                         AS JOB_NAME         
      ,CASE WHEN [enabled] =1 THEN 'Enabled'
                              ELSE 'Disabled' END     AS JOB_ENABLED      
      ,j.category_id                                  AS JOB_CATEGORY_ID
      ,c.name                                         AS JOB_CATEGORY_NAME
      ,[description]                                  AS JOB_DESCRIPTION  
      ,date_created                                   AS DATE_CREATED     
      ,date_modified                                  AS DATE_MODIFIED
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
WHERE   job_id IN( SELECT job_id
                  FROM    msdb.dbo.sysjobsteps
                  WHERE   database_name = 'YourSQLDba' )
 ORDER BY j.name

步骤3：执行YourSQLDba脚本，如果你没有做任何扩展。那么直接执行脚本即可，不需要修改任何脚本。

如果你研究过了脚本，那么你们会发现，YourSQLDba是会保留原来的数据的，在代码里面，你会看到在删除YourSQLDba数据库之前，脚本会将先前YourSQLDba的数据保存到临时表。

If databasepropertyEx('YourSQLDba','status') IS NOT NULL -- db is there
Begin
  -- save data about some YourSqlDba tables
  If object_id('tempdb..##JobHistory') is not null Drop table ##JobHistory;
  If object_id('tempdb..##JobLastBkpLocations') is not null Drop table ##JobLastBkpLocations;
  If object_id('tempdb..##JobSeqCheckDb') is not null Drop table ##JobSeqCheckDb;
  If object_id('tempdb..##TargetServer') is not null Drop table ##TargetServer;
  If object_id('tempdb..##JobSeqUpdStat') is not null Drop table ##JobSeqUpdStat;
  If object_id('tempdb..##NetworkDrivesToSetOnStartup') is not null Drop table ##NetworkDrivesToSetOnStartup;
 
  -- If table exists in previous version save its content
  If Object_id('YourSqlDba.Maint.JobHistory') IS NOT NULL
    Select * Into ##JobHistory From YourSqlDba.Maint.JobHistory
 
  If Object_id('YourSqlDba.Maint.JobLastBkpLocations') IS NOT NULL
    Select * Into ##JobLastBkpLocations From YourSqlDba.Maint.JobLastBkpLocations
 
  If Object_id('YourSqlDba.Mirroring.TargetServer') IS NOT NULL
    Select * Into ##TargetServer From YourSqlDba.Mirroring.TargetServer
 
  If Object_id('YourSqlDba.Maint.JobSeqUpdStat') IS NOT NULL
    Select * Into ##JobSeqUpdStat From YourSqlDba.Maint.JobSeqUpdStat
 
  If Object_id('YourSqlDba.Maint.JobSeqCheckDb') IS NOT NULL
    Select * Into ##JobSeqCheckDb From YourSqlDba.Maint.JobSeqCheckDb
 
  If Object_id('YourSqlDba.Maint.NetworkDrivesToSetOnStartup') Is NOT NULL
    Select * Into ##NetworkDrivesToSetOnStartup From YourSqlDba.Maint.NetworkDrivesToSetOnStartup

然后在YourSQLDba创建成功后，将数据导入到新建的表后，删除临时表。部分脚本如下所示：

-- if the table doesn't exists create the latest version
If object_id('Maint.JobSeqCheckDb') is null
Begin
  Declare @sql nvarchar(max)
  Set @sql =
  '
  Create table  Maint.JobSeqCheckDb
  (
    seq         int
  )
  Insert into Maint.JobSeqCheckDb values(0)
  '
  Exec (@sql)
 
  If Object_Id('tempdb..##JobSeqCheckDb') IS NOT NULL
    Exec
    (
    '
    Insert Into Maint.JobSeqCheckDb (seq)
    Select Seq
    From ##JobSeqCheckDb
    Drop table ##JobSeqCheckDb
    '
    )
End
GO

如果你做过扩展，例如我按天、周、月监控数据库的增长情况，那么我要在YourSQLDba升级过程中保留这些历史数据，那么就必须修改这部分脚本（这些涉及太多脚本，不宜在此贴过多脚本，在此不做过多探讨）。YourSQLDba脚本升级成功后，会有下面提示信息

步骤4：更新YourSQLDba_FullBackups_And_Maintenance和YourSQLDba_LogBackups作业。

首先，在YourSQLDba升级过程中，脚本并不会更新这两个作业。在前面的几个版本中，都不需要重新新作业YourSQLDba_FullBackups_And_Maintenance等，因为即使版本不一样，但是作业功能基本是一致的。但是像这样两个版本差别大的时候，作业里面执行的存储过程的参数都不一样了。例如5.0.2，里面全备的执行的存储过程为

exec Maint.YourSqlDba_DoMaint
  @oper = 'YourSQLDba_Operator'
, @MaintJobName = 'YourSQLDba: DoInteg,DoUpdateStats,DoReorg,Full backups'
, @DoInteg = 1
, @DoUpdStats = 1
, @DoReorg = 1
, @DoBackup = 'F'
, @FullBackupPath = 'M:\DB_BACKUP\FULL_BACKUP\' 
, @LogBackupPath = 'M:\DB_BACKUP\LOG_BACKUP\' 
-- Flush database backups older than the number of days
, @FullBkpRetDays = 1
-- Flush log backups older than the number of days
, @LogBkpRetDays = 2
-- Spread Update Stats over 7 days
, @SpreadUpdStatRun =1
-- Maximum number of consecutive days of failed full backups allowed
-- for a database before putting that database (Offline).
, @ConsecutiveDaysOfFailedBackupsToPutDbOffline = 9999
-- Each database inclusion filter must be on its own line between the following quote pair
, @IncDb =
'
' 
-- Each database exclusion filter must be on its own line between the following quote pair
, @ExcDb =
'
' 
-- Each database exclusion filter must be on its own line between the following quote pair
, @ExcDbFromPolicy_CheckFullRecoveryModel =
'
' 

但是6.2.5.1里面，执行的存储过程如下所示，多了参数 @SpreadCheckDb，参数@ConsecutiveDaysOfFailedBackupsToPutDbOffline名字变了，如果不做修改，那么作业就会报错。

exec Maint.YourSqlDba_DoMaint
  @oper = 'YourSQLDba_Operator'
, @MaintJobName = 'YourSQLDba: DoInteg,DoUpdateStats,DoReorg,Full backups'
, @DoInteg = 1
, @DoUpdStats = 1
, @DoReorg = 1
, @DoBackup = 'F'
, @FullBackupPath = 'M:\DB_BACKUP\FULL_BACKUP\' 
, @LogBackupPath = 'M:\DB_BACKUP\LOG_BACKUP\' 
-- Flush database backups older than the number of days
, @FullBkpRetDays = 1
-- Flush log backups older than the number of days
, @LogBkpRetDays =2
-- Spread Update Stats over 7 days
, @SpreadUpdStatRun = 1
-- Spread Check DB without 'PHYSICAL_ONLY' over 7 days
, @SpreadCheckDb = 7
-- Maximum number of consecutive days of failed full backups allowed
-- for a database before putting that database (Offline).
, @ConsecutiveDaysOfFailedBackupsToPutDbOffline = 0
-- Each database inclusion filter must be on its own line between the following quote pair
, @IncDb =
'
' 
-- Each database exclusion filter must be on its own line between the following quote pair
, @ExcDb =
'
' 
-- Each database exclusion filter must be on its own line between the following quote pair
, @ExcDbFromPolicy_CheckFullRecoveryModel =
'
' 

而升级脚本并不会重建或更新作业，所以我们执行初始化脚本YourSQLDba.[Install].[InitialSetupOfYourSqlDba]更新作业。注意，执行该脚本后，你原来的配置参数都会使用初始化参数，例如（@FullBkpRetDays，@LogBkpRetDays…..等）,另外，像YourSQLDba_LogBackups作业的Schedule变为15分钟做一次事务日志备份，在执行脚本前，最好记录原来的参数，以免影响原来的备份策略。

EXEC YourSQLDba.[Install].[InitialSetupOfYourSqlDba] 
  @FullBackupPath = 'M:\DB_BACKUP\FULL_BACKUP\' -- full backup path destination
,@LogBackupPath = 'M:\DB_BACKUP\LOG_BACKUP\'   -- log backup path destination
,@email = 'xxx@xxxx.com'                  -- Email recipients (or distribution list)
,@SmtpMailServer = 'xxx.xxx.xxx.xxx'             -- Mail server that accept SMTP mail
,@ConsecutiveDaysOfFailedBackupsToPutDbOffline = 9999

另外，如果我们扩展了YourSQLdba的脚本，此时就必须添加相应存储过程,作业倒不必新建(因为YourSQLDba升级不会删除作业,但是由于YourSQLDba数据库重建了,所以作业里面调用的存储过程可能没有了)。

转载于:https://www.cnblogs.com/kerrycode/p/5579176.html

你可能感兴趣的文章