你的位置:首页 > 数据库

[数据库]YourSQLDba版本升级总结


    在使用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


clipboard

 

步骤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脚本升级成功后,会有下面提示信息

clipboard[1]

 

步骤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分钟做一次事务日志备份,在执行脚本前,最好记录原来的参数,以免影响原来的备份策略。

clipboard[2]

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数据库重建了,所以作业里面调用的存储过程可能没有了)。