怎么修改sqlserver 的备份路径
发布网友
发布时间：2022-04-09 05:25
共2个回答
懂视网
时间：2022-04-09 09:46
go
reconfigure;
go
sp_configure ‘xp_cmdshell‘, 1;
go
reconfigure;
go
Exec YourSQLDba.Maint.CreateNetworkDrives
@DriveLetter = ‘S:‘
@unc = ‘\192.168.xxx.xxxYourSQLDBABAKServer1‘
设置网络路径，必须开启数据库“xp_cmdshell”选项，否则就会有如下错误。
Exec YourSQLDba.Maint.CreateNetworkDrives
@DriveLetter = ‘S:‘
, @unc = ‘\192.168.xxx.xxxYourSQLDBABAKServer1‘
消息 15123，级别 16，状态 1，过程 sp_configure，第 62 行
The configuration option ‘xp_cmdshell‘ does not exist, or it may be an advanced option.
net use S: /Delete
net use S: \192.168.xxx.xxxYourSQLDBABAKServer1
15123: The configuration option ‘xp_cmdshell‘ does not exist, or it may be an advanced option.
消息 15123，级别 16，状态 1，过程 sp_configure，第 62 行
The configuration option ‘xp_cmdshell‘ does not exist, or it may be an advanced option.
如果你遇到下面错误信息，请检查你SQL SERVER服务的登录账号是否是NT账号或域账号。如果是默认的NT ServiceMSSQLSERVER则会遇到该错误提示。
可以将SQL Server服务的登录账号改为共享路径设置权限的域账号。那么接下来，修改一下作业YourSQLDba_FullBackups_And_Maintenance里面的配置信息就OK了
exec Maint.YourSqlDba_DoMaint
@oper = ‘YourSQLDba_Operator‘
, @MaintJobName = ‘YourSQLDba: DoInteg,DoUpdateStats,DoReorg,Full backups‘
, @DoInteg = 1
, @DoUpdStats = 1
, @DoReorg = 1
, @DoBackup = ‘F‘
, @FullBackupPath = ‘S:FULL_BACKUP‘
, @LogBackupPath = ‘S:LOG_BACKUP‘
-- Flush database backups older than the number of days
, @FullBkpRetDays = 1
-- Flush log backups older than the number of days
, @LogBkpRetDays =1
-- Spread Update Stats over 7 days
, @SpreadUpdStatRun =1
-- Maximum number of consecutive days of failed full backups allowed
-- for a database before putting that database (Offline).
, @ConsecutiveFailedbackupsDaysToPutDbOffline = 9999
-- Each database inclusion filter must be on its own line between the following quote pair
, @IncDb =
‘
‘
-- Each database exclusion filter must be on its own line between the following quote pair
, @ExcDb =
‘
‘
-- Each database exclusion filter must be on its own line between the following quote pair
, @ExcDbFromPolicy_CheckFullRecoveryModel =
‘
‘
如果SQL Server服务是LocalSystem启动，YourSQLDba备份时会报如下错误信息,你需要将SQL Server服务的登录身份改为域账号
BackupDiskFile::CreateMedia: 备份设备 ‘S:FULL_BACKUPMsDb_[2016-07-04_Mon]_database.BAK‘ 无法create。操作系统错误 5(拒绝访问。)。
在测试过程中发现YourSQLDba备份到共享路径对网络环境要求比较高，有几次在网络出现连续掉两个或两个以上包的时候，备份进程就出错，检查出错信息，发现如下错误信息。
<Exec>
<ctx>yMaint.backups</ctx>
<Sql>
backup database [WSS_Content_get_teams_tdc]
to disk = ‘S:FULL_BACKUPTest_[2014-11-11_18h49m05_Tue]_database.BAK‘
with Init, Format, checksum, name = ‘YourSQLDba:18h49: S:FULL_BACKUPTest_[2014-11-11_18h49m05_Tue]_database.BAK‘
</Sql>
<err>Error 3201, Severity 16, level 1 : Cannot open backup device ‘S:FULL_BACKUPTest_[2014-11-11_18h49m05_Tue]_database.BAK‘. Operating system error 53(The network path was not found.).
Error 3013, Severity 16, level 1 : BACKUP DATABASE is terminating abnormally.
</err>
</Exec>
YourSQLDba设置共享路径备份
标签：
热心网友
时间：2022-04-09 06:54
