欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-12-22 11:50:54
如果YourSQLDba设置过共享路径备份(具体参考博客YourSQLDba设置共享路径备份),有时候服务器重启后,备份就会出错,具体错误信息类似如下所示: Date 2019/9/25 10:10:00Log SQL Server (Current - 2019/9/25 3:06:00) Sou... ......

如果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