我有一个全新安装的 SQL Server 2016,我正在尝试将系统数据库重新定位到机器上的不同驱动器上,Drive:D
我运行了以下命令,但不断收到相同的错误。我能想到的唯一问题是文件权限,我的SQLSERVER
原始路径文件夹中有一个用户,我无法找到并归因于新位置。我用谷歌搜索过,所有帮助文章似乎都为服务器 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...