Click here to Skip to main content
13,193,048 members (68,229 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hope someone on here can help...

Our C: drive on one of our servers is getting a bit full so we decided to try to relocate the SQL databases to another drive on the same machine. SQL 2016 - Windows Server 2016. Note that this is a virtual server running under Hyper-V.

We followed the instructions to Detach the database, then copy the files to the desired location and then re-attach the database.

This all seemed to work except the database was set to 'Read Only'.
When trying to set read only to False in the options we got an error saying that there were insufficient permissions and the state could not be changed.

Reading a million posts about this everyone suggests that we need to add full permissions to NT Service/MSSQLSERVER.
That sounds reasonable but the security tab does not recognise ant NT Service entries.

Looking at the running services it definitely uses this as the service log on so why can we not select it when trying to allow full access to the moved files?

We ticked the box to show service accounts but still nothing.

What stupid mistake have I made this time?

Thanks

What I have tried:

Tried to add permissions via the file > properties > security settings but cannot find the SQL server account.
Posted 19-Jun-17 3:56am
dnibbo670
Updated 19-Jun-17 5:37am
Comments
0x01AA 19-Jun-17 12:03pm
   
dnibbo 20-Jun-17 4:05am
   
Hi thanks, not sure if it was that exact article but read several similar ones. Our problem is that the NT Service accounts don't show up when we click the 'Check names' button.
We even tried the 'Advanced' option and then clicked on 'Find Now' which shows all available accounts; again the NT Server ones do not show up.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

In the past I always found that copying SQL Server database files directly was looking for trouble, instead it's better to make a backup and restore that.

This StackOverflow answer might be what you are looking for: Best way to copy a database (SQL Server 2008) - Stack Overflow[^]
  Permalink  
v2
Comments
dnibbo 20-Jun-17 4:06am
   
But when you create a new database it will be in the default directory and therefore still need to be moved wouldn't it?

As it happens, SQL was able to read the data but not update it so we surmised that it was using the server\users permissions.
We changed that to full control and could then set read only to False.

Not entirely sure what the implications are here but it seems to work OK?

Thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web04 | 2.8.171017.2 | Last Updated 19 Jun 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100