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.
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.