YourSQLDba的共享路径备份遭遇重启问题
程序员文章站
2022-05-18 21:14:47
如果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