转载

YourSQLDba的共享路径备份遭遇重启问题

如果YourSQLDba设置过共享路径备份(具体参考博客YourSQLDba设置共享路径备份),有时候服务器重启后,备份就会出错,具体错误信息类似如下所示:

 

 

Date        2019/9/25 10:10:00
Log        SQL Server (Current - 2019/9/25 3:06:00)
 
Source        spid56
 
Message
BackupDiskFile::CreateMedia: Backup device 'M:\xxx\LOG_BACKUP\msdb_[2019-09-24_00h08m06_Tue]_logs.TRN' failed to create. Operating system error 3(系统找不到指定的路径。).

 

 

 

出现这个问题,需要使用Exec YourSQLDba.Maint.CreateNetworkDriv设置网络路径,即使之前设置过网络路径,查询[YourSQLDba].[Maint].[NetworkDrivesToSetOnStartup]表也有相关网络路径设置,但是确实需要重新设置才能消除这个错误。

 

 

EXEC sp_configure 'show advanced option', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
 
EXEC YourSQLDba.Maint.CreateNetworkDrives @DriveLetter = 'M:\',
    @unc = 'xxxxxxxxxx;
GO
 
 
sp_configure 'xp_cmdshell', 0;
GO
 
EXEC sp_configure 'show advanced option', 1;
GO
RECONFIGURE;

 

 

查看了一下 [Maint].[CreateNetworkDrives]存储过程,应该是重启过后,需要运行net use这样的命令进行相关配置。

 

 

USE [YourSQLDba]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [Maint].[CreateNetworkDrives] 
  @DriveLetter nvarchar(2) 
, @unc nvarchar(255) 
as
Begin
  Declare @errorN int
  Declare @cmd nvarchar(4000)
 
  Set nocount on
 
  Exec yMaint.SaveXpCmdShellStateAndAllowItTemporary 
 
  Set @DriveLetter=rtrim(@driveLetter)
  Set @Unc=rtrim(@Unc)
 
  If Len(@DriveLetter) = 1
    Set @DriveLetter = @DriveLetter + ':'
 
  If Len(@Unc) >= 1
  Begin
    Set @Unc = yUtl.NormalizePath(@Unc)
    Set @Unc = Stuff(@Unc, len(@Unc), 1, '')
  End
 
  Set @cmd = 'net use <DriveLetter> /Delete'
  Set @cmd  = Replace( @cmd, '<DriveLetter>', @DriveLetter)
  
 
  begin try 
    Print @cmd
    exec xp_cmdshell @cmd, no_output
  end try 
  begin catch 
  end catch
 
  -- suppress previous network drive definition
  If exists(select * from Maint.NetworkDrivesToSetOnStartup Where DriveLetter = @driveLetter)
  Begin
    Delete from Maint.NetworkDrivesToSetOnStartup Where DriveLetter = @driveLetter
  End
 
  Begin Try
    
    Set @cmd = 'net use <DriveLetter> <unc>'
    Set @cmd  = Replace( @cmd, '<DriveLetter>', @DriveLetter )
    
    Set @cmd  = Replace( @cmd, '<unc>', @unc )
    Print @cmd
    exec xp_cmdshell @cmd
 
    Insert Into Maint.NetworkDrivesToSetOnStartup (DriveLetter, Unc) Values (@DriveLetter, @unc)
    
    Exec yMaint.RestoreXpCmdShellState 
 
  End Try
  Begin Catch
    Set @errorN = ERROR_NUMBER() -- return error code
    Print convert(nvarchar, @errorN) + ': ' + ERROR_MESSAGE() 
    Exec yMaint.RestoreXpCmdShellState 
  End Catch
 
End -- Maint.CreateNetworkDrives
正文到此结束
本文目录