Move system database to a different disk

Question

Hi all,

I know the steps to move system databases to a new location. What I don't know is how to give the correct permission to SQL Server services to the new location.

Now my system database are in "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA"

I have a brand new disk G: and I want to recreate the directory tree and put the files into

"G:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA"

How to give the correct permission? My SQL Server starts with a domain account.

Answer 1

1) Right click on the disk and add  account under security tab

2) Add this account to group of Administrators 

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 2

Adding account to the local administrators group? But now SQL Server service account isn't in the local administrators group...

Answer 3

Add SQL Server to local admin account isn't best practice and is overkill.

Look at the old folder's security permissions in "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA" and see which accounts have which level of permissions/access.  Then you should be able to recreate those permissions on your new directories. 

Depending on how you are setup, SQL Server shouldn't need full access to the entire drive.  I'd limit it to just the folders it needs access to.

You might see something like this and possible another one for the SQLAgent.

EG:  [YOURSERVERNAME]\SQLServerMSSQLUser$[YOURSERVERNAME]$MSSQLSERVER

Here is the down and dirty on permissions.

http://msdn.microsoft.com/en-us/library/ms143504.aspx