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