Moved Master and MSDB Now Engine Service Will Not Start

SQL Server Administration
1

So I stopped all SQL services
Then copied the master and msdb db files to a temp drive.
The original drive was blown away and readded with a samller size which is why we did this.
I copied the db files back to original drive with the same exact folder structure. I compared bytes and they match the source files.

Now the SQL engine service will not start. Because SQL recognizes the drive is different is that right?
I thought this would work - is there anything i can do other than reinstall SQL Server?
The Application log shows ........ERRORLOG5(Access is denied.)

Thanks

2

Check the permissions on the new drive. Make sure the SQL account has permissions on all SQL drives.

1 Like
3

@ScottPletcher ScotttPletcher - It's running under account NT Service\MSSQL$instanceNameHere

When I try to add that account to folder permissions, get "no such account exists"

4

Sorry, I'm a DBA not a security/Windows person. I know the SQL account needs access to the drives/folders that contain SQL files, but I'm not the best person to state how to properly grant those permissions.

5

Two options:

  1. Change the SQL Service account to built-in "LOCALSYSTEM", that account should have the correct permissions.

  2. In Windows Explorer, right-click the folder where master and msdb are stored, choose "Properties", then go to the "Security" tab. Find the service account you are using for SQL Server in the list of accounts, click on it and click "Edit" button. Re-select the account in the new pop-up window and check "Full Control" in the lower window. Click OK until all pop-ups are closed.

  3. If you don't see the account in the list, click "Edit" button, then click "Add" button in the new pop-up. Once added, then continue with "Full Control" permissions.

1 Like
6

Ha, that's exactly what I did @robert_volk and it worked (Changed to Local System). Just now read your post.

I don't like these types of accounts. Should I advise my client to create a domain account for SQL engine and agent services? That's worked well for me in the past
Thanks..

7

The NT SERVICE accounts are specialized accounts that are created when you install SQL Server. You can get to those accounts through the security dialog by going to advanced and searching for 'NT SERVICE\MS'.

For future reference, when copying files like this you should use robocopy and make sure to include the flags to copy the security. And - instead of copying twice, add the new drive at the expected size - copy using robocopy - relabel the original drive to an unused letter and relabel the new drive to the original.

This also gives you the opportunity to validate the new structure before blowing it away, just in case you need to roll back.

1 Like
8

Good ideas, @jeffw8713 Curious, do you run sql services under the default accounts?

9

Generally I setup SQL with a domain account, but will be looking at using a managed service account for future installations

2 Likes
10

Same here for the domain accounts. What are some advantages of using domain accounts rather than local system or the default account? It's easier to assign folder permissions and SQL roles for one. Any others? Thanks

11

You really only need a domain account if your SQL Server needs to access network resources or remote services, like a UNC path for file sharing. I've defaulted to this kind of account but I really haven't needed it, and it's less secure than a local or managed account.

1 Like