2

I have a fresh install of SQL Server 2016 which i'm trying to relocate the system databases onto a different drive on the machine Drive:D I've ran the following commands but keep getting the same error. The only issue I can think of is the file permissions, i have an SQLSERVER user on my original path folder that I could not locate and attribute to the new locations. I've googled and all the help articles seem to specify the same instructions mostly for servers 2008R2 & 2012 has 2016 changed anything that prevents this from working properly?

Links Followed:
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

Uesrs: On many help articles they list the SQL instance user to be added into the new locations but i don't think SQL SVR 2016 has the same users as I don't see any instance user in my folders.

I did however remove the following users from disk D: Users/Everyone.

Folder permissions on drive 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')

Error:

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.
UserSN
  • 157
  • 3
  • 16

1 Answers1

1

Was missing NT SERVICE\MSSQLSERVER from the destination's folder permissions...

UserSN
  • 157
  • 3
  • 16