Sql

SQL SVR 2016 - 將系統數據庫重新定位到不同的驅動器(無效目錄錯誤消息 5121)

  • May 26, 2020

我有一個全新安裝的 SQL Server 2016,我正在嘗試將系統數據庫重新定位到機器上的不同驅動器上,Drive:D我執行了以下命令,但一直收到相同的錯誤。我能想到的唯一問題是文件權限,我的SQLSERVER原始路徑文件夾中有一個使用者,我無法找到並歸因於新位置。我用Google搜尋過,所有幫助文章似乎都為伺服器 2008R2 和 2012 指定了相同的說明,2016 是否更改了任何阻止其正常工作的內容?

遵循的連結:

https ://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases

https://ask.sqlservercentral.com/questions/119177/need-to-move- tempdb-from-c-drive-to-g-drive.html

使用者:在許多幫助文章中,他們列出了要添加到新位置的 SQL 實例使用者,但我不認為 SQL SVR 2016 具有相同的使用者,因為我在我的文件夾中沒有看到任何實例使用者。

但是,我確實從磁碟 D 中刪除了以下使用者:使用者/所有人。

驅動器 D 上的文件夾權限:

Administrators

System

CreatorOwner

TSQL:

ALTER DATABASE tempdb
   MODIFY FILE(NAME = 'tempdev', FILENAME = 'D:\MSSQL13.MSSQLSERVER\tempdb.mdf')

ALTER DATABASE tempdb
   MODIFY FILE(NAME = 'templog', FILENAME = 'D:\MSSQL13.MSSQLSERVER\templog.ldf')

ALTER DATABASE model
   MODIFY FILE(NAME = 'modeldev', FILENAME = 'D:\MSSQL13.MSSQLSERVER\model.mdf')

ALTER DATABASE model
   MODIFY FILE(NAME = 'modellog', FILENAME = 'D:\MSSQL13.MSSQLSERVER\modellog.ldf')

ALTER DATABASE msdb
   MODIFY FILE(NAME = 'MSDBData', FILENAME = 'D:\MSSQL13.MSSQLSERVER\MSDBData.mdf')

ALTER DATABASE msdb
   MODIFY FILE(NAME = 'MSDBLog', FILENAME = 'D:\MSSQL13.MSSQLSERVER\MSDBLog.ldf')

錯誤:

Msg 5121, Level 16, State 1, Line 5
The path specified by "D:\MSSQL13.MSSQLSERVER\tempdb.mdf" is not in a valid directory.
Msg 5121, Level 16, State 1, Line 8
The path specified by "D:\MSSQL13.MSSQLSERVER\templog.ldf" is not in a valid directory.
Msg 5121, Level 16, State 1, Line 11
The path specified by "D:\MSSQL13.MSSQLSERVER\model.mdf" is not in a valid directory.
Msg 5121, Level 16, State 1, Line 14
The path specified by "D:\MSSQL13.MSSQLSERVER\modellog.ldf" is not in a valid directory.
Msg 5121, Level 16, State 1, Line 17
The path specified by "D:\MSSQL13.MSSQLSERVER\MSDBData.mdf" is not in a valid directory.
Msg 5121, Level 16, State 1, Line 20
The path specified by "D:\MSSQL13.MSSQLSERVER\MSDBLog.ldf" is not in a valid directory.

目標文件夾權限中缺少 NT SERVICE\MSSQLSERVER…

引用自:https://serverfault.com/questions/890208