September 6, 2017 at 12:19 am
Hi all,
Tried moving log.ldf to another drive but hit error. Script and error as below...
ALTER DATABASE dbName set offline
ALTER DATABASE dbName
MODIFY FILE (NAME='dbName_log',FILENAME='L:\MSSQL\Data\dbName_log.ldf')
ALTER DATABASE dbName set online
Msg 5120, Level 16, State 101, Line 3
Unable to open the physical file "'L:\MSSQL\Data\dbName_log.ldf". Operating system error 5: "5(Access is denied.)".
File activation failure. The physical file name "'L:\MSSQL\Data\dbName_log.ldf" may be incorrect.
Msg 5181, Level 16, State 5, Line 3
Could not restart database "dbName". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Also tried give full permission to folder in L drive but failed.
September 6, 2017 at 12:33 am
Oh forgotten to mention that i moved the physical file to the new drive before set online.
September 6, 2017 at 12:37 am
Riic - Wednesday, September 6, 2017 12:19 AMHi all,Tried moving log.ldf to another drive but hit error. Script and error as below...
ALTER DATABASE dbName set offline
ALTER DATABASE dbName
MODIFY FILE (NAME='dbName_log',FILENAME='L:\MSSQL\Data\dbName_log.ldf')
ALTER DATABASE dbName set onlineMsg 5120, Level 16, State 101, Line 3
Unable to open the physical file "'L:\MSSQL\Data\dbName_log.ldf". Operating system error 5: "5(Access is denied.)".
File activation failure. The physical file name "'L:\MSSQL\Data\dbName_log.ldf" may be incorrect.
Msg 5181, Level 16, State 5, Line 3
Could not restart database "dbName". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.Also tried give full permission to folder in L drive but failed.
Use detach database and attache with move for the ldf
😎
September 6, 2017 at 12:51 am
Thanks Eirikur Eiriksson! You mean detach the db and do an ALTER command for mdf & ldf? I intend to put both file in a different drive.
And now i am using the same method to move my master, error again gosh!
September 6, 2017 at 1:11 am
Riic - Wednesday, September 6, 2017 12:51 AMThanks Eirikur Eiriksson! You mean detach the db and do an ALTER command for mdf & ldf? I intend to put both file in a different drive.
And now i am using the same method to move my master, error again gosh!
For the user databases, detach the databases, move the files to the desired destination and then use attach with move to point to the new locations
For moving the system databases, use alter database to point to the new location, stop the SQL Server Service, move the files and start the SQL Server Service.
😎
September 6, 2017 at 2:00 am
Operating System 5 Error is a windows permissions error. You need to resolve this or it won't work whatever you do in SQL.
Yo may need to detatch the db and then get someone with permissions to copy the file to that location. If you will not be granted access at the windows level to do this.
September 6, 2017 at 2:38 am
Eirikur Eiriksson - Wednesday, September 6, 2017 12:37 AMUse detach database and attache with move for the ldf
😎
Negative, the supported route frojm Microsoft is to use the ALTER DATABASE ... MODIFY FILE command.
It's not microsofts fault if permissions are not granted to the folder where the file has been moved to
😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 6, 2017 at 2:39 am
Riic - Wednesday, September 6, 2017 12:19 AMAlso tried give full permission to folder in L drive but failed.
How did you try to give permissions and to which account?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 6, 2017 at 3:33 am
Thanks you GURUs for the prompt response!
To give permission, i right click folder-->Properties-->Security tab-->give Full control to myself.
Am i doing the right thing? Or is there anything i have missed it?
September 6, 2017 at 5:10 am
Riic - Wednesday, September 6, 2017 3:33 AMThanks you GURUs for the prompt response!
To give permission, i right click folder-->Properties-->Security tab-->give Full control to myself.
Am i doing the right thing? Or is there anything i have missed it?
Permissions need to be granted to the service.
If you grant full control to NT SERVICE\MSSQLSERVER if you have a default instance, or NT SERVICE\MSSQL$<INSTANCENAME> if you have a named instance (replace <instancename> with the name of your instance), note you need to change the location scope to the computer and not the domain.
September 6, 2017 at 5:16 am
Riic - Wednesday, September 6, 2017 3:33 AMThanks you GURUs for the prompt response!
To give permission, i right click folder-->Properties-->Security tab-->give Full control to myself.
Am i doing the right thing? Or is there anything i have missed it?
permissions must be applied to the account the database engine runs under as directed by anthony
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 6, 2017 at 8:43 pm
Thank you anthony.green and Perry Whittle for your guidance. How do i change the scope of location? I am using a name instance. Do i grant full control to my name instance folder?
September 7, 2017 at 5:09 am
Riic - Wednesday, September 6, 2017 8:43 PMThank you anthony.green and Perry Whittle for your guidance. How do i change the scope of location? I am using a name instance. Do i grant full control to my name instance folder?
whatever location you choose needs the ACLs granted for the database engine svc account to access the files
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 7, 2017 at 5:36 am
Riic - Wednesday, September 6, 2017 8:43 PMThank you anthony.green and Perry Whittle for your guidance. How do i change the scope of location? I am using a name instance. Do i grant full control to my name instance folder?
On the folder where your LDF file is, give NT SERVICE\MSSQL$<INSTANCENAME> (replace <INSTANCENAME> with the name of your instance Full Control to that folder.
You need to change the location on the "Select Users, Computers, Groups or Service Accounts" dialog box to the local machine.
Or find the account in the directory listing that is running the SQL Server Service.
September 7, 2017 at 8:04 pm
Thank you anthony.green and Perry Whittle again! I may not have access right to change location to "Local Machine", will try again.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply