YourSQLDba 配置——修改备份路径-阿里云开发者社区

公司一直使用YourSQLDba做本地备份，磁带机将本地备份文件上带做异地容灾备份。近期整理、验证备份时发现本地备份目录命名五花八门

其中有历史原因，也有无规划化的缘故，看着这些五花八门的目录，越看越不顺眼。于是想统一规范化。备份目录统一为DB_BACKUP，完整备份位于FULL_BACKUP子目录，事务日志备份位于LOG_BACKUP子目录下。例如如下所示：

完整备份目录：G:\DB_BACKUP\FULL_BACKUP\

事务日志备份目录：G:\DB_BACKUP\LOG_BACKUP\

于是需要修改YourSQLDba备份路径，本来是一个简单的事情，但是在测试过程中，发现了一些小问题，特此记录一下：

问题1：在哪里修改备份路径？修改是否立即生效？

修改YourSQLDba的备份路径，需要修改两个作业YourSQLDba_FullBackups_And_Maintenance 和YourSQLDba_LogBackups的配置. 即修改调用的存储过程Maint.YourSqlDba_DoMaint中的@FullBackupPath和@LogBackupPath参数。如下所示：

YourSQLDba_FullBackups_And_Maintenance作业

 
  exec Maint.YourSqlDba_DoMaint 
  
    @oper = 'YourSQLDba_Operator' 
  
  , @MaintJobName = 'YourSQLDba: DoInteg,DoUpdateStats,DoReorg,Full backups' 
  
  , @DoInteg = 1 
  
  , @DoUpdStats = 1 
  
  , @DoReorg = 1 
  
  , @DoBackup = 'F' 
  
  , @FullBackupPath = 'G:\DB_BACKUP\FULL_BACKUP\' 
  
  , @LogBackupPath = 'G:\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). 
  
  , @ConsecutiveFailedbackupsDaysToPutDbOffline = 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 = 
  
  ' 
  
  '

YourSQLDba_LogBackups作业

 
  exec Maint.YourSqlDba_DoMaint 
  
    @oper = 'YourSQLDba_Operator' 
  
  , @MaintJobName = 'Log backups' 
  
  , @DoBackup = 'L' 
  
  , @FullBackupPath = 'G:\DB_BACKUP\FULL_BACKUP\' 
  
  , @LogBackupPath = 'G:\DB_BACKUP\LOG_BACKUP\' 
  
  -- Specify to user that full database backups are mandatory before log backups 
  
  , @NotifyMandatoryFullDbBkpBeforeLogBkp = 1 
  
  , @BkpLogsOnSameFile = 1 
  
  -- 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 = 
  
  ' 
  
  '

将@FullBackupPath和 @LogBackupPath 从D:\backup\修改为G:\DB_BACKUP\FULL_BACKUP\与G:\DB_BACKUP\LOG_BACKUP\后，修改后是否立即生效？修改过后，如果没有先做一次完整备份，事务日志备份文件（事务日志备份有可能半小时。一小时一次等多种选择）并不会在新目录G:\DB_BACKUP\LOG_BACKUP\生成，依然还是在旧目录G:\backup\下生成。直到完整备份备份后，事务日志备份才在新目录生效。这样就导致我必须等到旧目录数据文件完全上带后才能删除旧目录。但是为什么会这样呢？

从Maint.YourSqlDba_DoMaint追查开始，发现其调用[yMaint].[Backups]做备份，做事务日志备份的一段代码如下

 
  If @DoFullBkp = 1 And DATABASEPROPERTYEX(@DbName, 'Recovery') <> 'Simple' 
  
        Begin 
  
          Set @fileName = yMaint.MakeBackupFileName(@DbName, 'L', @LogBackupPath, @Language, @LogBkExt) 
  
          Set @sql = yMaint.MakeBackupCmd 
  
                     ( 
  
                       @DbName 
  
                     , 'L' -- say explicitely full backup command 
  
                     , @fileName 
  
                     , 1 
  
                     , @MaintJobName 
  
                     ) 
  
          -- Launch first log backup that creates the file that will be used 
  
          -- to stored log backups usually for the rest of the days unless 
  
          -- end-user launch Maint.SaveDbOnNewFileSet 
  
          Exec yExecNLog.LogAndOrExec 
  
            @context = 'yMaint.backups' 
  
          , @sql = @sql 
  
          , @Info = 'Log backups (init)' 
  
          , @JobNo = @JobNo 
  
          , @errorN = @errorN_BkpPartielInit output 
  
          -- Restore the backup to the mirror server if enabled 
  
          Exec yMirroring.QueueRestoreToMirrorCmd 
  
               @context = 'yMaint.backups (queue restore of log backup init)' 
  
             , @JobNo = @JobNo 
  
             , @DbName = @DbName 
  
             , @bkpTyp = N'L' 
  
             , @fileName = @fileName 
  
             , @MirrorServer = @MirrorServer 
  
             , @BrokerDlgHandle = @BrokerDlgHandle OUT 
  
          If @errorN_BkpPartielInit = 0 -- version 
  
          Begin 
  
            Update Maint.JobLastBkpLocations 
  
            Set lastLogBkpFile = @filename 
  
             , MirrorServer = @MirrorServer 
  
            Where dbName = @DbName 
  
            -- shrink the log after backup (the procedure acts depending on the size) 
  
            -- Exec yMaint.ShrinkLog @DbName, @JobNo 
  
          End 
  
        End

其中@fileName = yMaint.MakeBackupFileName(@DbName, 'L', @LogBackupPath, @Language, @LogBkExt)取值取决于@LogBackupPath，而@LogBackupPath来源于Maint.JobHistory中（如下所示）。每次全备在Maint.JobHistory中生成一条记录，而全备后不管事务日志备份多少次，只会在第一次事务日志备份时生成记录，所以即使修改了@FullBackupPath和 @LogBackupPath这两个参数的值，Maint.JobHistory表中最后一次事务日志备份记录的值依然为修改前的旧值。从而出现上面描述的问题。

 
  Select  
  
      @MaintJobName = JobName  
  
    , @DoFullBkp = DoFullBkp 
  
    , @DoLogBkp = DoLogBkp 
  
    , @FullBkpRetDays = FullBkpRetDays 
  
    , @LogBkpRetDays = LogBkpRetDays 
  
    , @NotifyMandatoryFullDbBkpBeforeLogBkp = NotifyMandatoryFullDbBkpBeforeLogBkp 
  
    , @BkpLogsOnSameFile = BkpLogsOnSameFile 
  
    , @FullBackupPath = yUtl.NormalizePath(FullBackupPath ) 
  
    , @LogBackupPath = yUtl.NormalizePath(LogBackupPath ) 
  
    , @FullBkExt = FullBkExt 
  
    , @LogBkExt = LogBkExt 
  
    , @ConsecutiveFailedbackupsDaysToPutDbOffline = ConsecutiveFailedbackupsDaysToPutDbOffline  
  
    , @IncDb = IncDb  
  
    , @ExcDb = ExcDb  
  
    , @jobStart = JobStart  
  
    , @MirrorServer = MirrorServer 
  
    , @JobId = JobId 
  
    , @StepId = StepId 
  
    From Maint.JobHistory Where JobNo = @JobNo

问题2：修改备份路径后，原路径下的备份文件能否在保留期后自动删除？

如下所示，在存储过程[yMaint].[Backups]里面删除过期备份时，@Path参数来自于@FullBackupPath 和@LogBackupPath

这两个参数的值来源于表Maint.JobHistory，而当修改了YourSQLDba备份路径后，如果没有做一次全备，对应记录FullBackupPath 与LogBackupPath字段的值都是原先的路径（因为事务日志备份的路径从Maint.JobHistory最后一次事务日志记录取值，也即原先的路径），做了一次全备后，其值都是修改后路径，这样就会导致以前备份删除不了的情况。此时只能手工删除。

 
  Select 
  
     @MaintJobName = JobName 
  
   , @DoFullBkp = DoFullBkp 
  
   , @DoLogBkp = DoLogBkp 
  
   , @FullBkpRetDays = FullBkpRetDays 
  
   , @LogBkpRetDays = LogBkpRetDays 
  
   , @NotifyMandatoryFullDbBkpBeforeLogBkp = NotifyMandatoryFullDbBkpBeforeLogBkp 
  
   , @BkpLogsOnSameFile = BkpLogsOnSameFile 
  
   , @FullBackupPath = yUtl.NormalizePath(FullBackupPath ) 
  
   , @LogBackupPath = yUtl.NormalizePath(LogBackupPath ) 
  
   , @FullBkExt = FullBkExt 
  
   , @LogBkExt = LogBkExt 
  
   , @ConsecutiveFailedbackupsDaysToPutDbOffline = ConsecutiveFailedbackupsDaysToPutDbOffline 
  
   , @IncDb = IncDb 
  
   , @ExcDb = ExcDb 
  
   , @jobStart = JobStart 
  
   , @MirrorServer = MirrorServer 
  
   , @JobId = JobId 
  
   , @StepId = StepId 
  
   From Maint.JobHistory Where JobNo = @JobNo

问题3：输出日志文件MaintenanceReport.txt路径在哪里修改？

YourSQLDba的输出日志文件MaintenanceReport.txt并不会随着@FullBackupPath和 @LogBackupPath 的修改会保存到新目录。这个文件的输出目录的值放置在msdb.dbo.sysjobsteps表中，对应记录的output_file_name字段。

可以通过两种方式来修改：

1 SQL脚本方式：

EXEC msdb.dbo.sp_update_jobstep @job_id=N'41662692-c7b2-47ae-8e07-df3eb12e1fe2', @step_id=1 ,

@output_file_name=N'G:\DB_BACKUP\MaintenanceReport.txt'

2：UI图形界面：

问题4：是否可以用其它方式。

可以修改@FullBackupPath和 @LogBackupPath参数值后，将旧备份文件拷贝到对应新目录，不过对备份比较大的服务器，会产生一定的IO消耗。修改修改Maint.JobLasstBkpLocations记录中的LastLogBkpFile和LastFullBkpFile字段, 以及Maint.JobHistory中最后一条记录的FullBackupPath与LogBackupPath。

 
  UPDATE Maint.JobLastBkpLocations set lastLogBkpFile='G:\DB_BACKUP\LOG_BACKUP\xxxx_[2014-07-15_17h48m08_Tue]_logs.TRN',  
  
  lastFullBkpFile='G:\DB_BACKUP\FULL_BACKUP\xxxx_[2014-07-15_17h48m07_Tue]_database.BAK' 
  
  WHERE ..... 
  
  UPDATE Maint.JobHistory SET FullBackupPath='G:\DB_BACKUP\LOG_BACKUP\', LogBackupPath='G:\DB_BACKUP\FULL_BACKUP' where JobNo=@JobNo

YourSQLDba 配置——修改备份路径

热门文章

最新文章

相关电子书

YourSQLDba 配置&mdash;&mdash;修改备份路径

热门文章

最新文章

相关电子书

YourSQLDba 配置——修改备份路径