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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/39264.html

標籤：SQL Server

上一篇：松軟科技web課堂:SQLServer之HAVING 子句

下一篇：關于SqlServer資料庫資料備份失敗的問題